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: 
MK9885
Master II
Master II

Create Current/Previous month flag

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.

7 Replies
lorenzoconforti
Specialist II
Specialist II

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?

Tanalex
Creator II
Creator II

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

Or

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

Anil_Babu_Samineni

In synthetically, it should be

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


Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MK9885
Master II
Master II
Author

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.

MK9885
Master II
Master II
Author

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

MK9885
Master II
Master II
Author

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?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.