Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic Words Based on Selection

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

11 Replies
swuehl
MVP
MVP

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)

    ))

Not applicable
Author

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!

swuehl
MVP
MVP

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.

Not applicable
Author

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]))))))

swuehl
MVP
MVP

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?

Not applicable
Author

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?

swuehl
MVP
MVP

=if( len(FiscalMonthYear), FiscalMonthYear,

     if( len(FiscalQuarterYear), FiscalQuarterYear,

         if( len(FiscalMonth), FiscalMonth,

            if(len(FiscalQuarter), FiscalQuarter,

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

     ))))

Not applicable
Author

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!

swuehl
MVP
MVP

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?