7 Replies Latest reply: Sep 25, 2018 10:13 AM by avneet taneja

# qliksense set analysis

hi

I am struck with set analysis. I know there are many post but not able to figure out

I want to calculate YTD

Sales Last fiscal year/ sales last YTD

I have a date column and have generated Fiscal calender also.

• ###### Re: qliksense set analysis

Don't panic, set expressions seems difficult at first sight, but then you'll love working with them.

Let's suppose you have a date field (you don't have to create variables); only a date field will do the job.

And let's suppose you've created a calendar in your script so you have Year,Month, Quarter and Week fields

YTD: if we select 12/04/2016: YTD will return our measure from 01/01/2016 to 12/04/2016

How we do that?

Suppose our measure is : sum(Sales)

1)First changes: sum({<date=,Year=,Month=,Quarter=>}Sales)

We add these to force Qlik to not take into consideration our selection of date for example.

Let me explain in better words, if you don't write the "date=" and select the date 12/04/2016; Qlik will filter all the data to that selected point and then return the sum(Sales) for the day 12/04/2016.

To prohibit this, we must write the date=.

2) Second change: sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}Sales)

Let explain this : date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}

We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

So we're working with the field :

a) date={    }

b) Now we wanna this date to be <=selected date which is max(date) ;

max(date) is a function so it needs an "=" sign:

=max(date)

when we have a '=' we add the \$ (before each calculation) : \$(=max(date) ) => this is 12/04/2016

Now we add the <= so we'll have :  <=\$(=max(date) )

for the second part, we want our date to be >=01/01/2016 which is the start of the year:

a) same approach, we use the YearStart function that returns the start of the selected year: >=\$(=YearStart(Max(date)))

Now our expression is : from : date={    }

to : date={>=\$(=YearStart(Max(date)))<=\$(=Max(date))}

Now, in a set expression, if we wanna work with the year 2016 for example which is numeric: we call it without quotes:

Year={2016}

If we wanna focus on a country, Tunisia for example which is a string: we call it with quotes

Country={'Tunisia'}

In our case, we focusing on a range of dates created by an expression, so we surround it by double quotes:

date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}

Final expression for YTD:

sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}Sales)

Same approach for MTD:

sum({<date=,Year=,Month=,Quarter=, date={">=\$(=MonthStart(Max(date)))<=\$(=Max(date))"}>}Sales)

Hope this helps,

Omar,

• ###### Re: qliksense set analysis

Thanks omar for explaining it but since I am using fiscal year i.e. 1st april

• ###### Re: qliksense set analysis

could you attach a sample app with mock data showing what are you trying to achieve ?

• ###### Re: qliksense set analysis

Hi Avneet,

Make two indicators called _indLastFiscalYTD and _indLastYTD. Give them an 1 when it is YTD. Then add this formula.

Sum({\$< _indLastFiscalYTD = {1} >} [Sales Last fiscal year]) / Sum({\$< _indLastYTD = {1} >} [sales last YTD])

• ###### Re: qliksense set analysis

Hi thanks for replying but I didn't get

_indLastFiscalYTD and _indLastYTD

• ###### Re: qliksense set analysis

You can do this for the YTD and make it also for the fiscal year.

let vYtdMmDd = date(\$(vToday),'MMDD');

if(date(Date,'MMDD')& ''<='\$(vYtdMmDd)',1,0)      as  _indYTD,