topic Re: Nested If statements in straight table in QlikView

Nested If statements in straight table

Tue, 02 Feb 2016 21:25:57 GMT

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!

Re: Nested If statements in straight table

sunny_talwar — Tue, 02 Feb 2016 21:45:41 GMT

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?

Re: Nested If statements in straight table

Oleg_Troyansky — Tue, 02 Feb 2016 22:29:14 GMT

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!

Re: Nested If statements in straight table

Wed, 03 Feb 2016 14:44:19 GMT

Thank you Oleg, This works perfectly!