Using set analysis

[shirleyk]

Hello,

I am new to set analysis, may i know if the following can be handled by set analysis or there is other way to solve? Thanks.

I have a data table which contains all the sales data of each shop, like the followings:

Region          Shop   Year   Month       Sales

Region1           A      2013   May         100

Region1           A      2013   Jun          110

….

Region1           A      2014   Nov         120

….

Region1           A      2015   Nov         130

Region2          B       2015   Feb          200

Region2          B       2015   Mar          210

…

Region2          B       2015   Nov          220

I would like to create a summary table that display the sum of the sales of those shops containing sales in both last year and this year only.

e.g. Current month = Nov 2015, the summary table would be like this:

Region                 Sales in Nov 2015                Sales in Nov 2014

Region1                            130                                    120

Region2                               0                                        0

[Anonymous]

see these:

set analysis YTD calculation

Set Analysis for Rolling Periods

Set analysis to find current year, year-1, year-2

[shirleyk]

Thanks for your quick reply!

I've been able to get sales of current year and year -1. However, i don't know how to filter the shops that have sales in both period. Could you please advise? Thanks.

Here is the expression i used for each fields,

Region =Region    

Sales in Nov 2015 = Sum( {$<Year={'$(=Max(Year))'},  Month = {'$(=Max(Month))'}  >}  Sales)

Sales in Nov 2014 = Sum( {$<Year={'$(=Max(Year)-1)'},  Month = {'$(=Max(Month))'}  >}  Sales)

[tresesco · Accepted solution]

Try like:

Sum( {$<Shop=P({<Year={'$(=Max(Year))'},  Month = {'$(=Max(Month))'}  >} ) >

          * <Shop=P({<Year={'$(=Max(Year)-1)'},  Month = {'$(=Max(Month))'}  >} ) > }  Sales)

[shirleyk]

I can get the figures now. Thanks so much for your help!

[thakkarrahul01]

Hi,

As you have shown in an example table you want to compare month year i.e. Nov 2015. I will recommend to extract this using MonthName function on date field(same as one you have used to extract Month and Year fields).

Once you have that you can write store you current month in a variable in script:

vCurrentMonth  =     MonthName(Today(1));  //which will give you Dec 2015 on todays date

Once you have this variable in place you can use it in expressions

=Sum({<MonthYearField={"=$(vCurrentMonth)"}>}Sales)   //Dec 2015

=Sum({<MonthYearField={"=$(MonthName(AddMonths($(vCurrentMonth),-12))) "}>}Sales)  //Dec2014

Kind Regards,

Rahul

[shirleyk]

Many thanks for the suggestion. Let me try then.