topic Re: Creating a flag to recognize the current year in QlikView

Creating a flag to recognize the current year

jason_nicholas — Tue, 02 Jan 2018 16:41:30 GMT

I have a function in my master calendar load script that isn't working as designed. I would appreciate any advice on where I might have gone wrong. Thanks in advance for your help.

The goal is to have charts display only current year data (with the Current Year Flag added to set analysis), EXCEPT in January, where I want the previous December data to remain displayed for month-over-month comparisons. In other words, charts should be January through December normally, but December through December in January.

The script function is below, It is intended to look at the data date and determine and create a flag:

IF the data year is this year

OR if the data month is December AND the data year is last year AND the current month is January

IF(Year([TempDate])=Year(Today()) or (NUM(Month([TempDate]))= 12 and Year([TempDate])=Year(Today()-1) and Month(Today())=1),1,null()) as [Current Year Flag],

Re: Creating a flag to recognize the current year

sunny_talwar — Tue, 02 Jan 2018 16:49:39 GMT

May be this

If(

(Month(Today()) > 1 and Year([TempDate]) = Year(Today())) or

(Month(Today()) = 1 and Year([TempDate]) = Year(Today())-1), 1) as [Current Year Flag],

Re: Creating a flag to recognize the current year

jason_nicholas — Wed, 03 Jan 2018 15:25:19 GMT

This resulted in all of the 2017 data being flagged as Current Year, and the 2018 data being disregarded. The chart only showed 2017 data, and not a December 2017 through December 2018 view.

I wonder if you have any other suggestions?

Maybe I am over complicating this, as I can just use two copies of the application (one 2017 focused, and the other 2018) to complete the task. However, If I can achieve the functionality I am looking for (application built to show current year data, but which carries over the previous December so there is a continual display of month-over-month change and no "clean slate" in January), it would be beneficial.

Re: Creating a flag to recognize the current year

sunny_talwar — Wed, 03 Jan 2018 15:42:00 GMT

Would you be able to provide some raw data and explain what you wish to be highlighted with a flag?

Re: Creating a flag to recognize the current year

Anonymous — Wed, 03 Jan 2018 15:46:43 GMT

Hi,

it's just a problem with the coomma position. This should works better :

IF(Year([TempDate])=Year(Today()) or (NUM(Month([TempDate]))= 12 and Year([TempDate])=Year(Today())-1 and Month(Today())=1),1,null()) as [Current Year Flag],

Re: Creating a flag to recognize the current year

antonaks — Wed, 03 Jan 2018 15:57:20 GMT

Hi! Try this:

LET DateNow = date(today(1)); //The current date

	if(InYearToDate(ID_DATE,'$(DateNow)',0),1,0) as CY_TD, //Flag of the current year
	if(InYearToDate(ID_DATE,'$(DateNow)',-1),1,0) as PY_TD,//Flag of the current period in the previous year
	if(InYear(ID_DATE,'$(DateNow)',-1),1,0) as PY_F, //Full previous year flag

ID_DATE is a date what you check.

Re: Creating a flag to recognize the current year

jason_nicholas — Wed, 03 Jan 2018 15:58:48 GMT

That's it!! Thank you.

I had the -1 inside of the parentheses for the YEAR function, where it needed to be outside.

Combine that with changing my dimension from 'Month' to 'MonthYear', and I got exactly what I was looking for.

Re: Creating a flag to recognize the current year

sunny_talwar — Wed, 03 Jan 2018 16:00:11 GMT

I have not read your response yet (I will read it)... but all I asked for is something like this

MonthYear Flag

Jan-2016 0

Feb-2016 0

Jan-2017 1

Feb-2017 1

Jan-2018 0

Feb-2018 0

Now I don't think that this is confidential or sensitive data which won't pass through company firewall .

Anyways, what I understood was that if Today is a day in Jan, you want to flag the last year. (Since today is Jan 3 2018, flag Jan 1 till Dec 31 2017). Once we move two Feb 1st 2018, flag Jan to feb...

Is any of the above sounds like what you need?

Now reading what you have

Re: Creating a flag to recognize the current year

jason_nicholas — Wed, 03 Jan 2018 16:10:01 GMT

I see what you mean. I removed the detailed text comment and can provide the answer to your question for future posterity (as the answer has been found)

If the current month is January:

MonthYear Flag

Jan-2017 0

Feb-2017 0

Dec-2017 1

Jan-2018 1

Feb-2018 1

If the current month is February through December:

If the current month is January:

MonthYear Flag

Jan-2017 0

Feb-2017 0

Dec-2017 0

Jan-2018 1

Feb-2018 1

Re: Creating a flag to recognize the current year

sunny_talwar — Wed, 03 Jan 2018 16:13:40 GMT

Life became so much easier once you showed what you wanted... anyways you already got what you wanted so I am not going to waste your time, but keep this thread in mind... most of the time the sample we are asking for is a mocked up dummy data which can show what you sort of have. This information is never confidential and can help us understand your problem and resolve is sooner then later

All the best my friend.

Best,

Sunny