Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day,
I have a quarterly report that aggregates months data based on the quarter selected.
I have this expression on my object and it just doesn't return any thing,
is there some incorrect in my expression?:
Sum({<Year={"$(=Year(MAX(Date)))"},
Date={">=$(=MonthStart(AddMonths(Max(Date),-2)))<=$(=MonthEnd(AddMonths(Max(Date),+0)))"}
>}WRITTEN_OFF_AMT)
Your assistance will be highly appreciated,
Mbini
Try this:
Sum({<Year, Date={"$(='>=' & Date(MonthStart(Max(Date), -2), 'DateFieldFormatHere') & '<=' & Date(MonthEnd(Max(Date), 0), 'DateFieldFormatHere))"}>}WRITTEN_OFF_AMT)
I tried this:
Sum({<//Year,
Date={"$(='>='& Date(MonthStart(Max(Date),-2),'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date), 0),'DD/MM/YYYY'))"}>}WRITTEN_OFF_AMT)
And I still dont get anything!!
What are you getting and what do you expect to see?
for this:
Date(MonthStart(Max(Date),-2)
I get: 01/04/2015
Date(MonthEnd(Max(Date), 0)
I get 30/06/2015
but when I have it on the expression as a whole:
Sum({<Year={"$(=Year(MAX(Date)))"},
Date={">=$(=MonthStart(AddMonths(Max(Date),-2)))<=$(=MonthEnd(AddMonths(Max(Date),+0)))"}
>}WRITTEN_OFF_AMT)
I get no results
Would it be possible to share a sample?
its quite a huge app i wont be able to share it over the net
See if this helps:
Preparing examples for Upload - Reduction and Data Scrambling
I normally try to convert all the dates to numbers (and if is possible round numbers).
For example, I use Floor(Date) AS Date and another table if I want to show formated dates in the dashboard.
With this solution I can opperate with dates without format problems.
Try to use the dates without format.