Focal Point
[SOLVED] percentage for MTM ,YTD and Rolling 12 months

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8147003986

February 13, 2018, 01:29 PM
v_ani
[SOLVED] percentage for MTM ,YTD and Rolling 12 months
Hello,

I have a request to calculate percentage. I couldn't figure it out yet, I appreciate any one have thoughts or suggestion ...

1.Month-to-Month - EX.%Percentage for same month last year 201702.
2.Year-to-date - EX:201701-201711
3.Rolling 12 months - EX:201612 - 201711

Thank you

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
February 13, 2018, 01:38 PM
Danny-SRL
v_ani,
Could you be a bit more specific?
Maybe post an example of the data?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

February 14, 2018, 07:41 AM
MartinY
1- Extract the data as you need it which is going to be the last 13 months and have it summarized per month. Then hold it

Using above extraction :

2- Calculate the percentage for this month vs last year same month - this is where you need the 13th months : 201802 need to be compared with 201702. Then hold it.

3- Calculate the year to date percentage. Need to sum first month of the year thru where we are at. Then hold it.

4- Calculate the rolling 12 months percentage. Need to sum current month up to 12 months ago. Then hold it.

5- You can then MORE all percentage files together (assuming that you have generates the same file format) and create your report.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
February 16, 2018, 08:54 AM
v_ani
Thanks for your reply, I able to work on the R12months I am trying to get the month order like this format on x-axis
12 01 02 03 04 05 06 07 08 09 10 11 currently I have the below define field it showing the order but i want to show label like this
12 01 02 03 04 05 06 07 08 09 10 11 i appreciate if you have alternate solution.

 COMPUTE MNSORTR12/A5V=IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '01' THEN 'M01'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '02' THEN 'M02'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '03' THEN 'M03'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '04' THEN 'M04'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '05' THEN 'M05'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '06' THEN 'M06'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '07' THEN 'M07'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '08' THEN 'M08'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '09' THEN 'M09'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '10' THEN 'M10'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '11' THEN 'M11'
ELSE IF EDIT(YEARMONTH ,'$$$$$$$$$99') EQ '12' THEN 'EP12'
ELSE '0'; 



WebFOCUS 8
Windows, All Outputs
February 16, 2018, 09:09 AM
Tony A
There's always alternatives, it is down to whether you can & how you wish to obtain your values -

SET PAGE = NOLEAD
DEFINE FILE GGSALES
  MNTH/M  = DATE;
  YEAR/YY = DATE;
  SORTORD/I3 = ((MNTH LE 11) * MNTH) + 1;
END
TABLE FILE GGSALES
  SUM DOLLARS
   BY MNTH
WHERE YEAR EQ 1996
HEADING
"Sorted by Month"
END
TABLE FILE GGSALES
  SUM DOLLARS
   BY SORTORD
   BY MNTH
WHERE YEAR EQ 1996
HEADING
"Sorted by SortOrd"
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
February 16, 2018, 09:43 AM
v_ani
Thanks Tony A it works!


WebFOCUS 8
Windows, All Outputs