Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
ktdewit1976
Contributor
Contributor

If-statement for Previous 12 Months

In my Master Calendar in my Edit Script I have used the following:

If(Datum <= Today() And Datum >= SetDateYear(Today(), Year(Today())-1), 1, 0) As Last12Months

Now I want to add a similar If-statement for Previous 12 Months. How do I script this (in a similar way as above)?

I am using this to be able to compare data for the last 12 months and the previous 12 months a year earlier. In a bar chart for example.

Labels (3)
2 Solutions

Accepted Solutions
sunny_talwar

I would try these instead

Last12Months

If(Datum <= Today() And Datum >= AddYears(Today(), -1), 1, 0) as Last12Months

Previous12Months

If(Datum <= AddYears(Today(), -1) And Datum >= AddYears(Today(), -2), 1, 0) as Previous12Months

View solution in original post

sunny_talwar

Try using a sort expression like this

Max(MonthYear)

If that still doesn't work, please share a sample file

View solution in original post

13 Replies
pradosh_thakur
Master II
Master II

May be this
If(Datum <= SetDateYear(Today(), Year(Today())-1) And Datum >= SetDateYear(Today(), Year(Today())-2), 1, 0) As Previous12Months
Learning never stops.
Marcos_rv
Creator II
Creator II

try this :

If(Datum <= Today() And Datum >= AddMonths(Today(), -12), 1, 0) As Last12Months

Regards!!!

sunny_talwar

I would try these instead

Last12Months

If(Datum <= Today() And Datum >= AddYears(Today(), -1), 1, 0) as Last12Months

Previous12Months

If(Datum <= AddYears(Today(), -1) And Datum >= AddYears(Today(), -2), 1, 0) as Previous12Months
ktdewit1976
Contributor
Contributor
Author

Now the graph looks like this. I did use MonthYear though instead of Month. When I use Month it does give the bars for each month next to each other. But the month January should be last in row, and it shows first in row. How do I solve this?

 

Last and previous months.jpeg 

 

sunny_talwar

May be use Month as a dimension instead of MonthYear here

ktdewit1976
Contributor
Contributor
Author

But then January 2019 shows on the left instead of the right. It is supposed to be a dynamic graph that changes when the date changes.

 

Data per month.jpeg

sunny_talwar

Use a sort expression to fix this... it shouldn't be too hard to use a sort expression. If you are unable to do this, please share a sample so that we can try out and get you the right sort expression

ktdewit1976
Contributor
Contributor
Author

I tried all different ways of sorting, but none gives me sort by date. I guess because it only recognizes Month when using that as a Dimension?

sunny_talwar

Try using a sort expression like this

Max(MonthYear)

If that still doesn't work, please share a sample file