Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shirleyk
New Contributor II

Using set analysis

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Using set analysis

Try like:

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

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

6 Replies
balrajahlawat
Esteemed Contributor

Re: Using set analysis

shirleyk
New Contributor II

Re: Using set analysis

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)

MVP
MVP

Re: Using set analysis

Try like:

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

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

shirleyk
New Contributor II

Re: Using set analysis

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

thakkarrahul01
Contributor

Re: Using set analysis

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
New Contributor II

Re: Using set analysis

Many thanks for the suggestion. Let me try then.

Community Browser