topic Re: Setting Master Calendar for Current Month and Previous Month in QlikView

Setting Master Calendar for Current Month and Previous Month

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi -

I have a data file with separate columns for Month and Year (i.e., there is no combined field that concatenates the two together) which is causing a bit of difficulty when trying to establish a master calendar.

Basically all of my data is from a prior month, for example if today's month is November, my latest data (known as our "current" data) will be from October. If this month is December, our "current" data will actually be from November, and so on...

How can I set a variable to make the "current" month match my data source? In other words, even though today's month is actually November, I want my current month to read October, because that is the last month of data we have in our system.

Obviously what I then need would be a "prior" month variable (showing September in the above example).

Re: Setting Master Calendar for Current Month and Previous Month

hallquist_nate — Tue, 21 Nov 2017 21:57:55 GMT

I would put this into your script and see if this returns the results you are looking for.

inmonth(Date, Today() , -1) as [Current Month]

You should get a "-1" for last month.

If you want to create a filter for Current Month, you can use an IF statement. If you want to use a 1/0, just multiply the formula by a -1 and that should turn the results positive. If you want to find the month prior, just use the following formula...

inmonth(Date, Today() , -2) as [prior Month]

Good Luck,

Nate

Re: Setting Master Calendar for Current Month and Previous Month

Anonymous — Wed, 22 Nov 2017 12:44:10 GMT

Thanks Nate. I have incorporated that into my script, but am still experiencing some issues, particularly when I try to use an IF statement in my charts.

I have the following IF statement: =if(CurrentMonth>= -1, sum(JRNL_LINE_CNT), ' ' )

because I don't want to return any values if the data does not fall within the CurrentMonth (hopefully that makes sense). What is happening is that I am getting an error that shows no data at all.

Any ideas what I'm doing wrong?

Re: Setting Master Calendar for Current Month and Previous Month

Mark_Little — Wed, 22 Nov 2017 13:29:37 GMT

Hi Claire,

I would flag them in your master calendar,

Depends on how your data is laid out, but you will want something like.

IF(MONTH(DATE) = MONTH(ADDMONTH(TODAY(),-1)),1,0) AS CurrentMonthFlag.

then your set Analysis for the sum of current month would be

SUM({<CurrentMonthFlag={1}>}JNRL_LINE_CNT)

Mark