Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Example:
Current selection using GetFieldSelections(Date, '/',1 ) i have >=1/1/2016<=7/27/2016
What would be the syntax to to get : >=1/1/2015<=7/27/2015 (same selected period minus 1 year).
I'm using datepicker in my app to make my selections. Can be various selections, for example if i choose 1 day, please show me same day last yer etc. etc. Hope it makes sens
Any help would be much appreciated.
Thank you in advance
oredas
I'm not quite sure why you are using GetFieldSelections() instead of just Date? Using just the Date field, 1 year ago is:
=AddMonths(Date,-12)
-Rob
I'm not quite sure why you are using GetFieldSelections() instead of just Date? Using just the Date field, 1 year ago is:
=AddMonths(Date,-12)
-Rob
Thanks for quick reply Rob,
Thing is, because it is an array (from-to) , =AddMonths(Date,-12) only works when you select single date, unless i'm missing something . Would you be so kind to elaborate. Guess i need to set min and max?
Regards,
oredas
Thanks for the hint Rob i was able to solve it with this string:
='>=' & Date(min(addmonths(Date,-12))) & '<=' & Date(max(addmonths(Date,-12)))
Just gonna need to add if statement if no dates selected, to show full dates from start to finish (otherwise will go back 1 year)
Much appreaciated
oredas
May be like this:
If(GetSelectedCount(Date) = 0, '>=' & Date(Min(Date)) & '<=' & Date(Max(Date)), '>=' & Date(min(addmonths(Date,-12))) & '<=' & Date(max(addmonths(Date,-12))))
Thanks Sunny for an answer,
Now i'm kinda stumped how to use this in calculation.
I created 2 variables vCalendarDateFrom for Date(min(addmonths(Date,-12))) and vCalendarDateTo for Date(max(addmonths(Date,-12)))) and in expression i use it like this:
sum({<fact_type = {'sales_fact'},Date={">=$(=vCalendarDateFrom)<=$(=vCalendarDateTo)"} >}sales_amount_cc)
But i'm getting nowhere, as it seems syntax is correct, but i'm getting 0.
All comments are much appreciated
oredas
How about using this:
Sum({<fact_type = {'sales_fact'}, Date={"$(=If(GetSelectedCount(Date) = 0, '>=' & Date(Min(Date)) & '<=' & Date(Max(Date)), '>=' & Date(min(addmonths(Date,-12))) & '<=' & Date(max(addmonths(Date,-12)))))"}>} sales_amount_cc)
That is exactly what i needed
Cudos to you Sunny
oredas