Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

Please suggest.

Regards,

Kam

5 Replies
OmarBenSalem

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:

DATE={">=$(=YearStart(addYears(max(DATE),-1)))<=$(=addYears(max(DATE),-1))"}


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,

mato32188
Specialist
Specialist

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

ECG line chart is the most important visualization in your life.
Not applicable
Author

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

OmarBenSalem

Glad that we helped you,

If you're question has been answered, please close the thread by marking the correct answer as so and te helpful answers as helpful (under actions)

Thank Koka

Sai_Mohan
Contributor III
Contributor III

Hi,

I have doubt on carry forwarding a task.

There is two status(complete and pending) . I have to calculate in weekly basis. if my work of that week is pending then those tasks should be carried to my next week and it has to be continued until all of my task is done.

Example:

Assume there is 100 tasks in Week1(w1) .i have completed only 20 in that week, so remaining 80 should be carried to next week. 

In w2 , they may add extra 10 tasks with that and overall pending would be 90 so i have to calculate a measure for individual week how many task is completed and outstanding in a line chart.

Thanks for the solution,

Sai.