Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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,
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
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
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
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.