topic Re: Dynamic Words Based on Selection in QlikView

Dynamic Words Based on Selection

Fri, 19 Jun 2015 18:45:01 GMT

Hi everybody!

Thanks for taking the time to read this post. I need to make the words in a text box change based on the user's selection. I have a master calendar that links fiscal year, fiscal quarter, and fiscal month with my data. I need the words to represent the selection. So if a user selects 2014, the words need to say 2014. If the user selects March and 2014, the words need to say Mar-14 (like how it's formatted in my FiscalMonthYear column in my master calendar), and so on and so forth. I've tried these possibilities and have gotten the following results:

//displays -1 =if(([Fiscal Year] or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth), (FiscalYear or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth), '-')

// displays - =GetFieldSelections(FiscalMonthYear)

// displays - =if(GetFieldSelections(FiscalMonthYear), FiscalMonthYear)

// displays -1 =[Fiscal Year] or FiscalMonth or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear

//displays -1 =if([Fiscal Year], [Fiscal Year]) or if(FiscalMonthYear, FiscalMonthYear)

// displays -1 =if(isnull(FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarter or FiscalQuarterYear),'Fiscal Period', FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarterYear or FiscalQuarter)

//= displays - GetFieldSelections(FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarter or FiscalQuarterYear)

//displays -1 =if([Fiscal Year] or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth, FiscalYear or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth, '-')

// displays year =if([Fiscal Year] or FiscalMonthYear, [Fiscal Year] , FiscalMonthYear)

Thanks in advance for your help!

Re: Dynamic Words Based on Selection

swuehl — Fri, 19 Jun 2015 19:46:15 GMT

If all your fields have a numeric representation, you can use something like

=alt(Date, YearMonth, Year)

It's important that you order the arguments finest granularity first, largest granularity last, so it will display Date when a single date is available / selected, then check for single YearMonth, then check for single Year.

If you have non-numeric values, same would apply for nested if() statements:

=if( len(Date), Date,

if( len(YearMonth), YearMonth,

if(len(Year), Year)

))

Re: Dynamic Words Based on Selection

Fri, 19 Jun 2015 19:59:58 GMT

That definitely got me closer! Thank you! Now I have:

=if(len(FiscalMonth), FiscalMonth,

if(len(FiscalQuarter), FiscalQuarter,

if( len([Fiscal Year]), [Fiscal Year],

if( len(FiscalMonthYear), FiscalMonthYear,

if( len(FiscalQuarterYear), FiscalQuarterYear,)))))

But it will only show one thing at a time even if multiple selections are made. So if I select 2014, it will display 2014. If I select January, it will display Jan. But if I select both, it will just display Jan. What am I doing wrong? Thanks again!

Re: Dynamic Words Based on Selection

swuehl — Fri, 19 Jun 2015 20:21:44 GMT

It may be just a question of how you prioritize the fields you want to show:

In my point of view, FiscalMonthYear is more granular / specific than a Month or Year:

=if( len(FiscalMonthYear), FiscalMonthYear,

if( len(FiscalQuarterYear), FiscalQuarterYear,

if( len(FiscalMonth), FiscalMonth,

if(len(FiscalQuarter), FiscalQuarter,

if( len([Fiscal Year]), [Fiscal Year])

))))

I am a little unsure how to get the quarters in sequence, so you may want to rotate things a little.

Re: Dynamic Words Based on Selection

Fri, 19 Jun 2015 20:28:46 GMT

That worked! Final question: how do I add an if no selection, display Fiscal Period? I tried writing:

=if(isnull('Fiscal Period'),

if(len(FiscalMonthYear), FiscalMonthYear,

if(len(FiscalQuarterYear), FiscalQuarterYear,

if(len(FiscalMonth), FiscalMonth,

if(len(FiscalQuarter), FiscalQuarter,

if(len([Fiscal Year]), [Fiscal Year]))))))

Re: Dynamic Words Based on Selection

swuehl — Fri, 19 Jun 2015 20:34:20 GMT

If there is no selection, is there only one single possible value for Fiscal Period?

If there are multiple values possible, which one should be shown?

Re: Dynamic Words Based on Selection

Fri, 19 Jun 2015 20:39:24 GMT

Sorry, I think it was unclear. Right now if there is no selection, it displays a hyphen. I want the words "Fiscal Period" to be there instead of - . Any ideas?

Re: Dynamic Words Based on Selection

swuehl — Fri, 19 Jun 2015 20:42:58 GMT

=if( len(FiscalMonthYear), FiscalMonthYear,

if( len(FiscalQuarterYear), FiscalQuarterYear,

if( len(FiscalMonth), FiscalMonth,

if(len(FiscalQuarter), FiscalQuarter,

if( len([Fiscal Year]), [Fiscal Year], 'Fiscal Period' )

))))

Re: Dynamic Words Based on Selection

Fri, 19 Jun 2015 20:47:07 GMT

For some reason it displays 2014 instead of Fiscal Period. I don't know why as that doesn't make any sense. I triple-checked to make sure all the selections were reset. Any idea why? Thank you so much for your help!

Re: Dynamic Words Based on Selection

swuehl — Fri, 19 Jun 2015 20:51:41 GMT

The epression does not check explicitely for selected values, just for single possible values.

How many possible FY do you have in your app? Only a single one, 2014?

Re: Dynamic Words Based on Selection

Fri, 19 Jun 2015 20:55:37 GMT

There are 2013, 2014, and 2015.

Re: Dynamic Words Based on Selection

swuehl — Fri, 19 Jun 2015 21:36:42 GMT

Hm, don't know what's causing this. Could you upload a small sample QVW?