topic Re: MonthName problem in QlikView

MonthName problem

sayanpwc — Wed, 23 May 2018 12:24:04 GMT

Hi guyz,

Facing a problem with my monthname field (MONTHS).

My requirement is to when activate the sheet this MONTHS field should always be selected < current month.

For ex: if current month is May, value in MONTHS field should be selected from Jan,Feb,Mar,Apr.

The values in MONTHS fields are like January,February,March ....

I dont have any numeric field corresponding to this MONTHS field. without doing anything in the script end can we achieve this in trigger?

How can I do that?? Please assist. It is urgent,

Re: MonthName problem

shansundar — Wed, 23 May 2018 13:59:16 GMT

Yes you can.

=If(Month(Date#(MONTHS, 'MMMM'))<Month(Today()), Date#(MONTHS, 'MMMM'))

Thanks,

Shan S

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 09:45:55 GMT

Its not working. I have put on activate sheet trigger ' select in filed' MONTHS field and under search string

=If(Month(Date#(MONTHS, 'MMMM'))<Month(Today()), Date#(MONTHS, 'MMMM'))

but its not working. Please help what or where should i write to get all the months selected while activating that sheet.

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 09:52:35 GMT

MONTHS alone should not work, because December 2017 is a date smaller than May 2018, yet it will not display, because December is higher month than May.

You need to use a date field instead and the function AddMonths() as the filter for the selections. For example:

AddMonths(Today(), -6) will return the date of today 6 months back.

Or you will have to create in the script a numeric value for each distinct month you have in the data model, so December 2017 is month 1, January 2018 is month 2 and so on.

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 09:55:42 GMT

yes I know that. I wanted to achieve it thorugh front end using the MONTHS field only that contains string values like January,February... so that is not possible right?

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 09:58:49 GMT

I don't see how if you only have a string. You still could use a Pick() and Match() combination to get numeric values but how do you then distinguish the year? Unless this is an application for one year only?

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 10:00:05 GMT

Yes forget about the year as of now. Its only about one year month

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 10:08:09 GMT

Then use a Pick() or Match() or both:

Match(Date(Today(), 'MMMM'), 'Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember')

Will return the numeric value for the month, and

Match(MONTHS, 'Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember')

Will return the numeric value of the month in that field, assuming they are written with the same capitalization.

You can then compare and select when one is higher or lower than the other.

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 10:11:08 GMT

I want this in trigger while activating sheet. Where and how to write this can u pls tell me? Thanks

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 10:12:43 GMT

and also want to do in the front end everything not at the script level

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 11:12:27 GMT

OnActivateSheet are done on the sheet properties > Triggers.

I would use a select in field and then as a string the result of the comparison.

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 11:15:15 GMT

I don't know if it's possible to do it without any change whatsoever in the script. Maybe it is, it will be complex and difficult to maintain. In the script it would be as easy as creating a new field with the numeric value of the month, and then use this field to select when activating the sheet.

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 11:52:21 GMT

what should be the result of comparison expression under search string under trigger?

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 12:08:04 GMT

It could be a string with all the months, then using a Concat() function. When using multiple values, the select in field string must be formatted like

(Month1|Month2|Month3)

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 12:13:14 GMT

But i need to put the condition that on activate sheet it should be always less than current month.

For ex : if currrent month is May, the filter will select jan,feb,mar,apr all thse 4 month.

How to write this condition in search string under trigger. can u write the expression for me?

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 12:14:55 GMT

I cannot now. I could check coming weekend.

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 12:38:14 GMT

EDIT: You could try something like this in the search string, it is untested and I don't like it at all, but it should work.

='(' &

Pick(Match(Date(Today(), 'MMMM'), 'Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember'),

'Januar', // case of January, show something here?

'Januar',

'Januar|Februar',

'Januar|Februar|März',

'Januar|Februar|März|April',

'Januar|Februar|März|April|Mai',

)

& ')'

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 12:48:50 GMT

Thanks for your analysis but its not working. Where is the condition in this expression that will day i need to select months less than current month?

Re: MonthName problem

Miguel_Angel_Baeyens — Thu, 24 May 2018 13:30:19 GMT

Actually it works, now I have tested it.

This is what you have to put on the "Search String" expression dialog in the Select in Field action. Field will be MONTHS.

Obviously, you will have to adapt and capitalize the texts according to your own (e.g.: all values UPPERCASE and English instead of German).

Re: MonthName problem

sayanpwc — Thu, 24 May 2018 13:43:58 GMT

actually it will work but using a bit of different expression. I have done it. its a bit long but achievable.

=if(UPPER(Date(Today(), 'MMMM'))='DECEMBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='NOVEMBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='OCTOBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='SEPTEMBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='AUGUST',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='JULY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='JUNE',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='MAY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='APRIL',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='MARCH',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='FEBRUARY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='JANUARY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY'}>} MONTHS, '|') & ')',

))))))))))))