topic Re: Defauly selection trigger - previous + current year minus current month in QlikView

Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 09:55:34 GMT

I want to create a sheet trigger to select the previous year and the current year but not the current month.

Sample data is as follows

Year month

201511

201512

201601

201602

201603

201604

Selection I would want would be

201511

201512

201601

201602

201603

Re: Defauly selection trigger - previous + current year minus current month

richard_chilvers — Mon, 25 Apr 2016 10:00:32 GMT

Hi Matthew

One idea might be to have 2 actions in your trigger:

First - select the current month in the appropriate field

Second - SELECT EXCLUDED

HTH

Re: Defauly selection trigger - previous + current year minus current month

sunny_talwar — Mon, 25 Apr 2016 10:03:17 GMT

Select everything except the most recent month? May be something like this:

='<=' & Date(AddMonths(Max(YearMonth), -1), 'YYYYMM')

Re: Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 10:03:52 GMT

So select previous year and the current year

and

SELECT excluded current month?

makes sense

Re: Defauly selection trigger - previous + current year minus current month

sunny_talwar — Mon, 25 Apr 2016 10:05:35 GMT

Attached a sample with on open trigger

Re: Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 10:06:02 GMT

This selects every single year month, possibly because year month is a string not a date?

Re: Defauly selection trigger - previous + current year minus current month

sunny_talwar — Mon, 25 Apr 2016 10:09:14 GMT

Including the most recent one?

Re: Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 10:11:15 GMT

yes 201603

maybe because it is a string not a date?

even thought max should still work as is it technically still the MAX number

Re: Defauly selection trigger - previous + current year minus current month

richard_chilvers — Mon, 25 Apr 2016 10:12:25 GMT

Trigger action one will select only the current month.

Trigger action two reverses the selection, and therefore excludes the current month.

Might work - but I haven't tried it.

Re: Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 10:14:23 GMT

='<' & Max([Year period])

selects everything except 201603

so just need to use that and exclude anything thats 201412 backwards

Re: Defauly selection trigger - previous + current year minus current month

sunny_talwar — Mon, 25 Apr 2016 10:17:36 GMT

What is the logic here to exclude everything before 201412? Only show this year (except current month) and complete previous year?

I would recommend changing number to date using date#() function in the script

Re: Defauly selection trigger - previous + current year minus current month

Mon, 25 Apr 2016 10:18:40 GMT

In the trigger,

use select in field

Field : Year month

Search String : ='<=' & Date(max([Year month]),'YYYYMM')

Regards,

Anjali Gupta

Re: Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 10:20:14 GMT

yeh thats the logic

i was thinking..

='<' & Max([Year period]) & > left(Max([Year period]), 4) - 2

Re: Defauly selection trigger - previous + current year minus current month

sunny_talwar — Mon, 25 Apr 2016 10:26:59 GMT

Try this:

='<' & Max(YearMonth) & '>=' & Num#(Date(YearStart(Max(Date#(YearMonth, 'YYYYMM')), -1), 'YYYYMM'), '##')

Re: Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 10:40:33 GMT

How would i do this for actual dates?

Month

Jan-15

Feb-15 etc

same logic

i tried ='<' & Max(Month) & '>=' =year(Month)-2

Re: Defauly selection trigger - previous + current year minus current month

sunny_talwar — Mon, 25 Apr 2016 10:46:06 GMT

Something like this:

='<' & Date(Max(Month), 'MMM-YY') & '>=' & Date(YearStart(Max(Month), -1), 'MMM-YY'), '##')

Re: Defauly selection trigger - previous + current year minus current month

matthewp — Mon, 25 Apr 2016 10:49:53 GMT

Something missing i think

Garbage after expression','

='<' & Date(Max(Month), 'MMM-YY') & '>=' & Date(YearStart(Max(Month), -1), 'MMM-YY'), '##'

Re: Defauly selection trigger - previous + current year minus current month

sunny_talwar — Mon, 25 Apr 2016 10:55:05 GMT

My bad, just this:

='<' & Date(Max(Month), 'MMM-YY') & '>=' & Date(YearStart(Max(Month), -1), 'MMM-YY')