Nested If statements in straight table

[#1]

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!

[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 · Accepted solution]

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!

[#4]

Thank you Oleg, This works perfectly!