Current vs Previous Month with month as a dimension..but it should be for Jan, Dec, Nov(Current month vs previous two months)

[kavita25]

Hi,

I want to show Current month Sales and previous month sales with the month as a dimension..for 3 months (Current month & previous 2 months)

Its Urgent...

Please provide ur solution ASAP.

I achieved previous month vs current month, but how can I restrict for 3 months only.

Regards,

kavita

[sunny_talwar]

Try these two expressions:

1) =Sum({<Date = {"$(='>='&Date(AddMonths(Max(Date), -3), 'D/M/YYYY')&'<='&Date(AddMonths(Max(Date), -1), 'D/M/YYYY'))"}>}Act_Amt)

2)

=If(Sum({<Date = {"$(='>='&Date(AddMonths(Max(Date), -3), 'D/M/YYYY')&'<='&Date(AddMonths(Max(Date), -1), 'D/M/YYYY'))"}>}Act_Amt) > 0,

If(Num(Month) > 1, Before(Sum({<Month, [Month Year], Date, Year>}Act_Amt)), After(Sum({<Month, [Month Year], Date, Year>}Act_Amt), 11)))

[sunny_talwar]

You mentioned seeing Jan, Dec and Nov data. The max date in your attached database is Feb 2014. You don't want to see Feb data? and do you want to see all years of data or just data for the most recent year?

[sunny_talwar]

I case you want to see all years, you can try these expressions:

1) =Sum({<Month = {$(=Concat({<Date = {"$(='>='&Date(AddMonths(Max(Date), -3), 'D/M/YYYY')&'<='&Date(AddMonths(Max(Date), -1), 'D/M/YYYY'))"}>} Chr(39) & Month & Chr(39), ','))}>}Act_Amt)

2)

=If(Sum({<Month = {$(=Concat({<Date = {"$(='>='&Date(AddMonths(Max(Date), -3), 'D/M/YYYY')&'<='&Date(AddMonths(Max(Date), -1), 'D/M/YYYY'))"}>} Chr(39) & Month & Chr(39), ','))}>}Act_Amt) > 0,

If(Num(Month) > 1, Before(Sum({<Month, [Month Year], Date, Year>}Act_Amt)), After(Sum({<Month, [Month Year], Date, Year>}Act_Amt), 11)))

[kavita25]

Yess.. Feb 2014...

[sunny_talwar]

Updated application attached.

[kavita25]

In latest year table, Feb previous month should be Jan-200

[sunny_talwar · Accepted solution]

Changed the expression for previous month:

=If(Sum({<Date = {"$(='>='&Date(AddMonths(Max(Date), -2), 'D/M/YYYY')&'<='&Date(Max(Date), 'D/M/YYYY'))"}>}Act_Amt) > 0,

If(Num(Month) > 1, Before(Sum({<Month, [Month Year], Date = {"$(='>='&Date(AddMonths(Max(Date), -3), 'D/M/YYYY')&'<='&Date(Max(Date), 'D/M/YYYY'))"}, Year>}Act_Amt)),

  After(Sum({<Month, [Month Year], Date = {"$(='>='&Date(AddMonths(Max(Date), -3), 'D/M/YYYY')&'<='&Date(Max(Date), 'D/M/YYYY'))"}, Year>}Act_Amt), 11)))

[kavita25]

Okay..il try n let u know..

I just want to confirm...If Feb comes. it will work ??

The output should be dynamic...as month comes

[sunny_talwar]

Feb seems to be already there so you are already seeing it now. If March comes you will see March. It is dynamic