Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sivakumarchv
Contributor II
Contributor II

Need Help in Previous Month and last 24 Months calculation

Hi,

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

Thanks,

Siva

Labels (1)
1 Solution

Accepted Solutions
CarlosAMonroy
Creator III
Creator III

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

View solution in original post

5 Replies
CarlosAMonroy
Creator III
Creator III

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
Contributor II
Contributor II
Author

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

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
Creator III
Creator III

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
Contributor II
Contributor II
Author

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