Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have some questions around Set Analysis:
If I have two fields for selection, where current month's sales amount is "ACTUAL_SALES_AMT(JPY)", and there are 3 financial years of records, where Financial Years 2012, 2013 & 2014 are called FY and the current months are called Month(MMM)
I need to present the above selections into 4 aggregated expressions as below:
1.) Total for the current month
2.) Total for the current 12months
3.) Total for the LAST YEAR same month period
4.) Total for the Previous month
I have expressions as below:
1.) Total for the current month: Sum(ACTUAL_SALES_AMT(JPY)
Easy enough
2.) Total for the current 12months:
Need help, am totally clueless how to handle this
3.) Total for the LAST YEAR same month period:
(Sum({$<FY={">$(=min(FY))<$(=max(FY))"}>}[ACTUAL_SALES_AMT(JPY)]))
The formula works by capturing the middle year (2013 only), which meets my current 2014 requirements, but if we select 2012 or 2013, this formula becomes redundant. How can I revise this to dynamically reference to the total sales amount of previous year's same month of the selected current month & FY?
I noticed when the previous year does not have records, it will return same total amt as the current month's, which is wrong, should have returned as zero.
I also tried revising to (Sum({$<FY={(FY)-1}>}[ACTUAL_SALES_AMT(JPY)])) but was completely useless then. Please advice.
4.) Total for the Previous month
Similar to the above, I just swap the FY with Month(MMM), and same problem as above.
(Sum({$<Month(MMM)={">$(=min(Month(MMM)))<$(=max(Month(MMM)))"}>}[ACTUAL_SALES_AMT(JPY)])) ,
Hi,
If you have date field try like this
1.) Total for the current month
=Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName)))<=$(=Date(Max(DateDimensionName)))'}>} [ACTUAL_SALES_AMT(JPY)])
2.) Total for the current 12months
=Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -11))<=$(=Date(Max(DateDimensionName)))'}>} [ACTUAL_SALES_AMT(JPY)])
3.) Total for the LAST YEAR same month period
=Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -12))<=$(=MonthEnd(Max(DateDimensionName), -12))'}>} [ACTUAL_SALES_AMT(JPY)])
4.) Total for the Previous month
=Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -1))<=$(=MonthEnd(Max(DateDimensionName), -1))'}>} [ACTUAL_SALES_AMT(JPY)])
Replace YearDimensionName=, MonthDimensionName=, DateDimensionName= field names with your actual field names.
Note : DateDimensionName and Today() date format should be same, if they are different then above expressions won't work.
Regards,
Jagan.