Need Help in Previous Month and last 24 Months calculation

[sivakumarchv]

Hi,

Any suggestion how I can get Previous Month and Last 20 months sales in straight table(or any table is fine)?

Thanks,

Siva

[CarlosAMonroy · Accepted solution]

Hi Siva,

You just need to add set analysis into your expression. In this case Sales for Last Month would be something like:

Last Month:
sum ( Date_Field={">=$(=Date(MonthStart(AddMonths(Max(Date_Field),-1)))) <=$(=Date(MonthEnd(AddMonths(Max(Date_Field),-1))))"}Sales )

Last 20 Months
sum ( Date_Field={">=$(=Date(MonthStart(AddMonths(Max(Date_Field),-20)))) <=$(=Date(MonthEnd(AddMonths(Max(Date_Field),-1))))"}Sales )

Hope that helps,
Carlos M

[sivakumarchv]

Hi Carlos,

Thanks for your prompt response. Is there a way to use this condition without aggregation functions. When I try to use these  I am not getting any result, I mean it's 0 always.

Thanks,

Siva

[Vegar]

You will need to adjust his expressions to your expressions and field names.

What does your current expression look like? What's the name of your date field?

[CarlosAMonroy]

Hi Siva,

If you have any calendar field selected, you may have to include that field into the set analysis. So it will exclude the selection and still show the values instead of 0.

Thanks,

Carlos

[sivakumarchv]

Hi Carlos,

Thanks, i just modified the logic and its working as expected.

Updated Formula:

sum({<Date_Field={">=$(=Date(Monthstart(Addmonths(today(),-25)),'M/D/YYYY'))<=$(=Date(MonthEnd(Addmonths(today(),-1)),'M/D/YYYY'))"}>}Sales)

Thanks,

Siva