Set Analysis Question - Previous Year Sales

Mon, 14 Jul 2014 03:15:24 GMT

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)])) ,

Re: Set Analysis Question - Previous Year Sales

jagan — Mon, 14 Jul 2014 03:35:31 GMT

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.

topic Set Analysis Question - Previous Year Sales in QlikView

Set Analysis Question - Previous Year Sales

Re: Set Analysis Question - Previous Year Sales