Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my data model, i have dateds from 01-Jan-2017 to 31-Dec-2020.
Current date is 15 Jul 2018, then i want to display default data using set analysis as current date -6 months and +17 months.
It means from 01-Jan-2018 to 31-Dec-2019.
I can not able to create variable using today() function cause if user select Aug-18 then it should as from 01-Feb-2018 to 31-Jan-2020 and I don't think so i can use max date also cause max date is 31-Dec-2020.
Please suggest if there is any possible option is available.
Thanks,
Villyee
You have three possible selection scenarios:
1) The user hasn't selected anything and then the current date - today - should be used as reference date.
2) The user has selected a single date and then that date should be the reference date.
3) The user has selected multiple dates and then either the first or the last date should be the reference date.
A) This can be done by creating four variables and using the last variable vRefDateRange in the set expression:
vRefDate:
=Date(If( GetSelectedCount(aDate)=0 , Today(1) , Min(aDate) ) )
vRefDateRangeMin:
=MonthStart(AddMonths(vRefDate,-6))
vRefDateRangeMax:
=MonthEnd(AddMonths(vRefDate,12))
vRefDateRange:
='>=$(vRefDateRangeMin)<=$(vRefDateRangeMax)'
Sum( {<aDate={"$(vRefDateRange)"}>} Sales )
B) Another way of making it more maintainable and testable using only a single variable could be:
vRefDate:
=AddMonths(If(GetSelectedCount(aDate)=0, Today(1) , Date(Min(aDate)) ) , $1 )
Sum( {<aDate={">=MonthStart($(vRefDate(-6)))<=MonthEnd($(vRefDate(12)))"}>} Sales )
C) You could of course hard-code the entire expression in a single set expression without using variables - although I think this is much harder to debug, maintain and reuse:
Sum( {<aDate={">=$(=MonthStart(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),-6)))<=$(=MonthEnd(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),12)))"}>} Sales )
Try using below expression
sum({<CalendarDate={">=($(=AddMonths(Date(Max(CalendarDate)),-6)))
=<($(=AddMonths(Date(Max(OrderDate)),+12)))"}>}SalesAmount)
Thanks Milind for your valuable reply.
But my requirements is not like this, in my calendar I have max date 31 Dec 2020 and as per your logic if I take
max(CalendarDate) and -6 then it will give me Jul-20 but I want current date -6 that is Jan-18 and that should be dynamic like if user click on Aug-18 then it should -6 from Aug-18 i.e. Feb-18.
Thanks.
You have three possible selection scenarios:
1) The user hasn't selected anything and then the current date - today - should be used as reference date.
2) The user has selected a single date and then that date should be the reference date.
3) The user has selected multiple dates and then either the first or the last date should be the reference date.
A) This can be done by creating four variables and using the last variable vRefDateRange in the set expression:
vRefDate:
=Date(If( GetSelectedCount(aDate)=0 , Today(1) , Min(aDate) ) )
vRefDateRangeMin:
=MonthStart(AddMonths(vRefDate,-6))
vRefDateRangeMax:
=MonthEnd(AddMonths(vRefDate,12))
vRefDateRange:
='>=$(vRefDateRangeMin)<=$(vRefDateRangeMax)'
Sum( {<aDate={"$(vRefDateRange)"}>} Sales )
B) Another way of making it more maintainable and testable using only a single variable could be:
vRefDate:
=AddMonths(If(GetSelectedCount(aDate)=0, Today(1) , Date(Min(aDate)) ) , $1 )
Sum( {<aDate={">=MonthStart($(vRefDate(-6)))<=MonthEnd($(vRefDate(12)))"}>} Sales )
C) You could of course hard-code the entire expression in a single set expression without using variables - although I think this is much harder to debug, maintain and reuse:
Sum( {<aDate={">=$(=MonthStart(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),-6)))<=$(=MonthEnd(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),12)))"}>} Sales )
Thanks a lot petter-s
You're welcome - glad to be of help