[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

Posts: 73 | Registered: April 06, 2016

Danny-SRL

Virtuoso

posted

Hide Post

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

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Show Post

MartinY

Virtuoso

posted

Hide Post

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

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Show Post

v_ani

Gold member

posted

Hide Post

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

Posts: 73 | Registered: April 06, 2016

Ignored post by v_ani posted

Show Post

Tony A

Expert

posted

Hide Post

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