Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Piet was quicker than me to reply we are basically proposing the same solution.