Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis syntax


Hi

I would like my chart to include information between dates specified by 2 variables but only before another date specified by another variable

I have from and to dates specified by the user in an input box (vFromDate and vToDate).  I also have another variable which is loaded called vProvDate

If vProvDate is 23/05/15 and the From and To dates are 02/05/15 and 30/05/15 then I want to include data from 02/05/15 to 23/5/15

If vProvDate is 23/05/15 and the From and To dates are 02/05/15 and 16/05/15 then I want to include data from 02/05/15 to 16/5/15

If vProvDate is 23/05/15 and the From and To dates are 30/05/15 and 13/06/15 then I don't want to include any data

>= vFromDate <=vToDate but also <=vProvDate

How can I achieve this in set analysis?

Thanks

A

4 Replies
markmccoid
Partner - Creator II
Partner - Creator II

Hi Adrian,

I think this will give you what you are looking for:

SUM({<PostingCalendarDate={">=$(fromDate)<=$(toDate)"}>*<PostingCalendarDate={"<=$(proDate)"}>} AMOUNT)

The Set Analysis above will first look for transactions between your from and to dates and then intersect that with transactions less than the proDate.

Hope this helps.

Mark

stigchel
Partner - Master
Partner - Master

I would create another variable e.g. vToDefDate where you use your conditions to select the correct enddate, then use this variable in set analysis.

vToDefDate

=if(vProvDate<vToDate,vProvDate,vToDate)

Expression

Sum({<Date={">=$(=Date(vFromDate))<=$(=Date(vToDefDate))"}>} Sales)

See example attached

cesaraccardi
Specialist
Specialist

Hi Adrian,

I think you will need another variable to check which date should be the final To date:

vToDateFinal: =if(vProvDate > vToDate, vToDate, vProvDate)

and then you can use a set analysis expression like this:

sum({$<Date={'=>$(vFromDate)<=$(vToDateFinal)'}>} Sales)

Hope this helps,

Cesar

cesaraccardi
Specialist
Specialist

Piet was quicker than me to reply we are basically proposing the same solution.