topic Re: Create Current/Previous month flag in QlikView

Create Current/Previous month flag

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

Hi,

I'd like to create month flag based on the date field I've.

I can use if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag

and

if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag

But instead of today() I want to use my on date/month field

Thanks.

Re: Create Current/Previous month flag

lorenzoconforti — Thu, 19 Oct 2017 21:28:59 GMT

Hi, can you please explain what you mean by "But instead of today() I want to use my on date/month field"?

Do you have another date field in your database (apart from TempDate) that you want to use in the InMonth function?

Re: Create Current/Previous month flag

Tanalex — Thu, 19 Oct 2017 23:12:22 GMT

if(InMonth(TempDate, <yourdatefield> ,-1), 1, 0) as LastMonthFlag

If(Month(<yourdatefield>) = Month(now())-1,1,0) AS LastMonthFlag

Re: Create Current/Previous month flag

Anil_Babu_Samineni — Fri, 20 Oct 2017 01:33:34 GMT

In synthetically, it should be

InMonth (timestamp, base_date, period_no[, first_month_of_year])

Re: Create Current/Previous month flag

MK9885 — Mon, 23 Oct 2017 12:45:59 GMT

If(Month(<yourdatefield>) = Month(now())-1,1,0) AS LastMonthFlag

The above expresion givs me Sep as last month which is correct, however it is giving Sep for ll the years. Where I just need Sep from 2015 for example.

There are 2 date fields one is loading until 2017 and another is loading until 2015 and I want to show last month of 2015 only.

Re: Create Current/Previous month flag

MK9885 — Mon, 23 Oct 2017 12:47:46 GMT

I have 2 date fields in DB.
One is loading until 2015 and another until 2017.

Flag is working if I use Today() but there is no data in 2017 previous month.

Rather there is data in 2015 Sep Month which is the last loaded month.

So I like to show last loaded month in 2015 only as a flag

Re: Create Current/Previous month flag

MK9885 — Mon, 23 Oct 2017 12:48:33 GMT

The fields inside your expression are confusing to me...

I do not have those fields in my table

Period no maybe I have it but rest?

Re: Create Current/Previous month flag

Peter_Cammaert — Mon, 23 Oct 2017 13:07:35 GMT

You can use a variable to indicate what you consider to be the current month. Either tie this variable to an input field annex calendar to make it easy to modify its value. Note that variables keep their values throughout reloads if the load script doesn't change them.

Or in your load script, start by calculating the "Current Month" based on what data you are loading and/or chat data is of interest to you. Then store that date in the same variable and proceed.