topic Re: Need Help in Previous Month and last 24 Months calculation in QlikView

Need Help in Previous Month and last 24 Months calculation

sivakumarchv — Sat, 16 Nov 2024 04:34:32 GMT

Hi,

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

Thanks,

Siva

Re: Need Help in Previous Month and last 24 Months calculation

CarlosAMonroy — Fri, 01 Feb 2019 22:02:07 GMT

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

Re: Need Help in Previous Month and last 24 Months calculation

sivakumarchv — Sat, 02 Feb 2019 16:48:25 GMT

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

Re: Need Help in Previous Month and last 24 Months calculation

Vegar — Mon, 04 Feb 2019 05:27:40 GMT

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?

Re: Need Help in Previous Month and last 24 Months calculation

CarlosAMonroy — Mon, 04 Feb 2019 16:20:52 GMT

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

Re: Need Help in Previous Month and last 24 Months calculation

sivakumarchv — Mon, 04 Feb 2019 21:06:10 GMT

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