topic Re: Need help with latest year flag in QlikView

Need help with latest year flag

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

Trying to create a simple flag in a calendar that filters the date between the last date of the previous month (so 2018-05-31 if max date = today) and the first day of the current month - 12 months (so 2017-06-01 if max date = today).

It should be dependent upon the max date in the selection.

I tried this in script and I only get errors and I dont understand why. When i tried the functions in text box they returned correct values....

if(Date>=MonthStart(Max(Date), - 12) and Date<=MonthEnd(Addmonths(Max(Date),-1)),'1') as LatestYear

Re: Need help with latest year flag

avinashelite — Wed, 20 Jun 2018 09:31:41 GMT

You cannot use max directly in the script ....try like this

if(Date>=MonthStart(today(), - 12) and Date<=MonthEnd(Addmonths(today(),-1)),'1') as LatestYear

Re: Need help with latest year flag

jyothish8807 — Wed, 20 Jun 2018 09:35:51 GMT

Hi Joe,

If you want to make it run based on the month selection, then you should write this logic on the UI and not on backend.

try like this: Create a variable in variable overview

vMaxDate= max(Date)

Then expression:

sum({<Date={'>=$(=date(monthstart(Addmonths($(vMaxDate),-12)))'}<=$(date(monthend(Addmonths($(vMaxDate),-1)))'}>}Sales)

Note: Make sure the date format is correct

Br,

Re: Need help with latest year flag

joeallen — Wed, 20 Jun 2018 09:39:14 GMT

Thanks Jyothish. That works well for a more dynamic solution.

Re: Need help with latest year flag

avinashelite — Wed, 20 Jun 2018 09:59:04 GMT

But the above expression doesn't work in script rite ? as per your question you needed something in the script level !!

Re: Need help with latest year flag

jyothish8807 — Wed, 20 Jun 2018 10:01:10 GMT

Yes, this is correct.

He wanted a expression which changes on the month selection he does on the UI also, so i suggested him to do it on the UI.

Br,

Re: Need help with latest year flag

avinashelite — Wed, 20 Jun 2018 10:04:01 GMT

jyothish8807 your answer is correct I feel he question made the confusion

Re: Need help with latest year flag

jyothish8807 — Wed, 20 Jun 2018 10:06:54 GMT

ya bro