[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 youThis 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