Yearly Rolling 12 months calculations

[Anonymous]

I have an application as attached where i am calculating the rolling 12 months (MAT) for the years Aug 2014 and Aug 2015.  The expressions are hard coded, which means that i need to adjust them as time goes on when data is updated.  How can this be improved without hard coding through the use of say variables or any other suitable means?.  No date selections will be made.  There is a calendar in the application.

Regards.

[HirisH_V7]

Hi,

For last 12 months you can use expression like this,

Your date field,

Date = {">=$(=MonthStart(Max(Date), -11))<=$(=MonthEnd(Max(Date)))"}

Hope This Helps,

Thanks,
Hirish

[Kushal_Chawda]

try below expression

=Num((Sum({< [Date Dispensed] = {">=$(=monthstart(addmonths(max([Date Dispensed]),-11)))<=$(=max([Date Dispensed]))"}, Year=, Month= >} [MedAid Amount] + [Shortfall])), '$##,##0.00')

[Anonymous]

Thanks Hirish

What about for the last year and the last two years?

[Anonymous]

Thanks Kush

What about for the last year and the last two years?

[HirisH_V7]

Hi,

current year : Sum({$<Year={$(#=Max(Year))}>}Sales)

Previous One Year : Sum({$<Year={$(#=Max(Year)-1)}>}Sales)

Previous Two Year : Sum({$<Year={$(#=Max(Year)-2)}>}Sales)

Hope this Helps,

Cheers,

Hirish

[Kushal_Chawda]

for last two year

Num((Sum({< [Date Dispensed] = {">=$(=monthstart(addmonths(max([Date Dispensed]),-23)))<=$(=addmonths(max([Date Dispensed]),-11))"}, Year=, Month= >} [MedAid Amount] + [Shortfall])), '$##,##0.00')

[Anonymous]

Hi Hirish

The expressions do not take into account the date function, why?  This gives wrong values.

[HirisH_V7]

Hi,

check out the attachment  .You can  Use your expression using date logic.

Hope this helps.

Thanks,

Hirish

[Anonymous]

Kush thank you very much.

The value of '11' in the expression for the last one year should be '12'.

I have attached a new application which compares the hard coded values with those from set analysis in text boxes to test them.  The hard coded values do not provided the same answers as those from set analysis.  Please assist in making sure all three calculations using different expressions are in agreement.

Regards.