topic Re: If-statement for Previous 12 Months in QlikView

If-statement for Previous 12 Months

ktdewit1976 — Sat, 16 Nov 2024 21:27:40 GMT

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.

Re: If-statement for Previous 12 Months

pradosh_thakur — Wed, 06 Feb 2019 13:54:30 GMT

May be this
If(Datum <= SetDateYear(Today(), Year(Today())-1) And Datum >= SetDateYear(Today(), Year(Today())-2), 1, 0) As Previous12Months

Re: If-statement for Previous 12 Months

Marcos_rv — Wed, 06 Feb 2019 13:57:37 GMT

try this :

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

Regards!!!

Re: If-statement for Previous 12 Months

sunny_talwar — Wed, 06 Feb 2019 13:57:47 GMT

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

Re: If-statement for Previous 12 Months

ktdewit1976 — Thu, 07 Feb 2019 10:08:23 GMT

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?

Re: If-statement for Previous 12 Months

sunny_talwar — Thu, 07 Feb 2019 12:25:42 GMT

May be use Month as a dimension instead of MonthYear here

Re: If-statement for Previous 12 Months

ktdewit1976 — Thu, 07 Feb 2019 13:31:06 GMT

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.

Re: If-statement for Previous 12 Months

sunny_talwar — Thu, 07 Feb 2019 13:40:17 GMT

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

Re: If-statement for Previous 12 Months

ktdewit1976 — Thu, 07 Feb 2019 13:44:41 GMT

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?

Re: If-statement for Previous 12 Months

sunny_talwar — Thu, 07 Feb 2019 13:51:35 GMT

Try using a sort expression like this

Max(MonthYear)

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

Re: If-statement for Previous 12 Months

pradosh_thakur — Thu, 07 Feb 2019 13:59:31 GMT

pick(Match(Month,month(today()),month(addmonth(today(),-1)),month(addmonth(today(),-2)),month(addmonth(today(),-3)),month(addmonth(today(),-4)),month(addmonth(today(),-5)),month(addmonth(today(),-6)),month(addmonth(today(),-7)),month(addmonth(today(),-8,)),month(addmonth(today(),-9)),month(addmonth(today(),-10)),month(addmonth(today(),-11))),1,2,3,4,5,6,7,8,9,10,11,12)

Try the above if month is the dimension . Please check the performance as it contains addmomth() 11 times.

Re: If-statement for Previous 12 Months

sunny_talwar — Thu, 07 Feb 2019 14:02:38 GMT

@pradosh_thakur wrote:

pick(Match(Month,month(today()),month(addmonth(today(),-1)),month(addmonth(today(),-2)),month(addmonth(today(),-3)),month(addmonth(today(),-4)),month(addmonth(today(),-5)),month(addmonth(today(),-6)),month(addmonth(today(),-7)),month(addmonth(today(),-8,)),month(addmonth(today(),-9)),month(addmonth(today(),-10)),month(addmonth(today(),-11))),1,2,3,4,5,6,7,8,9,10,11,12)

Try the above if month is the dimension . Please check the performance as it contains addmomth() 11 times.

You are really complicating it. I don't think we need this.

Re: If-statement for Previous 12 Months

ktdewit1976 — Thu, 07 Feb 2019 14:02:48 GMT

That worked. Thanks Sunny!

Re: If-statement for Previous 12 Months

pradosh_thakur — Thu, 07 Feb 2019 14:07:17 GMT

@sunny_talwar wrote:
You are really complicating it. I don't think we need this.

Yeah. It's really over complicated. Just thought too much i guess. I was trying to get jan to the last and didn't think of the simpler solution. 😄