topic Selecting a range of month names based on variable in QlikView

Selecting a range of month names based on variable

adamdavi3s — Wed, 13 Oct 2010 11:33:16 GMT

Hi,

This is probably a very stupid question...

I am trying to make a listbox selection based on my variable $max_period_name which is set during the load script

I want to select all month names up to and including the variable (using April as month 1) on a specific sheet

I've tried this through triggers and macros and I just don't seem to be able to get the correct syntax to make it work

This is an example of what I am trying to achieve, I couldn't think of a better way of demonstrating it


=if(max_period_name='Apr' THEN set month_name='Apr', ELSE
 if(max_period_name='May' THEN set month_name="'Apr','May'", ELSE
 if(max_period_name='Jun' THEN set month_name="'Apr','May','Jun'", ELSE
 if(max_period_name='Jul' THEN set month_name="'Apr','May','Jun','Jul'", ELSE ...

Selecting a range of month names based on variable

syed_muzammil — Wed, 13 Oct 2010 13:26:14 GMT

Hi,

In the load script make another field

num(Month)-4 as NewMonthNo

Now you have NewMonthNo as 1 for April.

Then create another variable

vTemp

=if(max_period_name='Apr',1,
if(max_period_name='May',2,
if(max_period_name='Jun',3,......

In your list box expression write the follwing expression

if(NewMonthNo>=1 and NewMonthNo<=vTemp,Month).

In the sort order sort by expression NewMonthNo.

Hope this will solve it.

Regards,

Syed Muzammil.

Selecting a range of month names based on variable

syed_muzammil — Wed, 13 Oct 2010 13:27:58 GMT

Change

num(Month)-4 as NewMonthNo

num(Month)-3 as NewMonthNo .

Selecting a range of month names based on variable

adamdavi3s — Wed, 13 Oct 2010 15:06:57 GMT

Hi,

Sorry I am at a complete loss as to how that helps me, my apologies if I have missed the point.

What I am trying to do is when the sheet is selected, make a selection in the list box which contains month names 'Apr' 'May' 'June' etc up to and including the latest month.

My sorting is fine as I just used wildmatch(month_name, 'Apr','May','Jun' etc)