5 Replies Latest reply: Apr 10, 2017 5:54 PM by omar bensalem

# How to calculate a value based on a date range in Qliksense

Hi All,

In a typical SQL if I would do this:

"case when B.date between B.financialyearstartdate and Max(B.date) then sum(A.salary) end"

where A, B are two different tables and they are having a valid join.

How could the same be achieved in Qliksense?

Regards,

Kam

• ###### Re: How to calculate a value based on a date range in Qliksense

Hi Koka,

Please refer to this thread, I explained in details how to proceed to build time expressions in Qlik step by step.

hope that will help:

YTD, MTD issue

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)

if we wanna focus on the YTD-1, we wanna alter this part:

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

to do so, we want to situate our self in the previous year, to do that, their is a function called addYears.

How we use it? addYears('04/12/2017',-1) = 04/12/2016;

So our expression will become:

With this, if the max(Date) in 2017 is 24/03/2017 (like in your case)

The YTD-1 will return the Sales from 01/01/2016 to 24/03/2016 .

Hope this helps,

Omar,

• ###### Re: How to calculate a value based on a date range in Qliksense

Hi Kam,

sum( {<B.date = {">=\$(=max(B.financialyearstartdate))  <=\$(=max(B.Date))"}>} Salary)

Try this expression in front end (use KPI object) and see, if it returns you relevant number. If not, come back and we can progress on that later on.

BR

Martin

• ###### Re: How to calculate a value based on a date range in Qliksense

Hi Martin and Omar,

Thank you very much for your quick turnaround. The expression sum( {<B.date = {">=\$(=max(B.financialyearstartdate))  <=\$(=max(B.Date))"}>} Salary) works as expected.

Regards,

Kam