# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

## 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
Esteemed Contributor

## Re: Using set analysis

New Contributor II

## Re: Using set analysis

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

## 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)

New Contributor II

## Re: Using set analysis

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

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