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

Nested If statements in straight table

Hi all, I am trying to set up a straight table that has multiple dimensions based on a field called Time. Basically if I choose Time = 'Year' then I want Month and Quarter calendar functions to be ignored. Also, if I choose Time='Quarter' I want Month to be ignored.

This expression works perfectly in a text box, but it defaults to the last Then statement in the straight table, in bold. Because of this, no matter what selection I make in Time, Month and Quarter are being excluded, which is not what I want.

This is  aggregated value

=if(Time = 'Month', Sum({$< Metric = {'New Business USD'}>}  Value),

  if(Time = 'Quarter', Sum({$< Metric = {'New Business USD'}, Month=>}  Value),

  Sum({$< Metric = {'New Business USD'}, Month=, Quarter=>}  Value)))

Point to point

=if(Time = 'Month', FirstSortedValue({<Metric = {'30+%'}>} Value, -MonthYear),

  if(Time = 'Quarter', FirstSortedValue({<Metric = {'30+%'},Month=>} Value, -MonthYear),

  FirstSortedValue({<Metric = {'30+%'},Month=,Quarter=>} Value, -MonthYear)))

Example with nested if's attached.

Thanks in advance!

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Mike,

the problem is caused by the logical condition Time = 'Month'. When it's used in the Monthly chart, it renders True only for selected (or available) Months, and it renders False for the excluded (unavailable) months. So, when it's False, it looks like the selection of Months is ignored, when in fact it's just the condition that doesn't work correctly.

The fix is rather simple: replace your existing conditions with this:

only(total Time) = 'Month'

and everything will work correctly.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

View solution in original post

3 Replies
sunny_talwar

Are you saying that when you click on Month, you want to see Quarter and Year as dimensions and when you click on Quarter you would want to see Year as a dimension also? I am having hard time understanding what the required output needs to be. Would you be able to clarify?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Mike,

the problem is caused by the logical condition Time = 'Month'. When it's used in the Monthly chart, it renders True only for selected (or available) Months, and it renders False for the excluded (unavailable) months. So, when it's False, it looks like the selection of Months is ignored, when in fact it's just the condition that doesn't work correctly.

The fix is rather simple: replace your existing conditions with this:

only(total Time) = 'Month'

and everything will work correctly.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Not applicable
Author

Thank you Oleg, This works perfectly!