Aggregate 3 months data on set analysis

[#1]

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

[sunny_talwar]

Try this:

Sum({<Year, Date={"$(='>=' & Date(MonthStart(Max(Date), -2), 'DateFieldFormatHere') & '<=' & Date(MonthEnd(Max(Date), 0), 'DateFieldFormatHere))"}>}WRITTEN_OFF_AMT)

[#3]

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!!

[sunny_talwar]

What are you getting and what do you expect to see?

[#5]

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

[sunny_talwar]

Would it be possible to share a sample?

[#7]

its quite a huge app i wont be able to share it over the net

[sunny_talwar]

See if this helps:

Preparing examples for Upload - Reduction and Data Scrambling

[jmvilaplanap]

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.