topic Set Analysis syntax in QlikView

Set Analysis syntax

Anonymous — Tue, 26 May 2015 14:50:08 GMT

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

Re: Set Analysis syntax

markmccoid — Tue, 26 May 2015 15:02:23 GMT

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

Re: Set Analysis syntax

stigchel — Tue, 26 May 2015 15:04:14 GMT

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

Re: Set Analysis syntax

cesaraccardi — Tue, 26 May 2015 15:20:32 GMT

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

Re: Set Analysis syntax

cesaraccardi — Tue, 26 May 2015 15:24:02 GMT

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