Set Analysis syntax

[Anonymous]

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

[markmccoid]

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]

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]

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]

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