Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
MVP & Luminary
MVP & Luminary

Re: Nested If statements in straight table

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!

3 Replies

Re: Nested If statements in straight table

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?

MVP & Luminary
MVP & Luminary

Re: Nested If statements in straight table

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

Re: Nested If statements in straight table

Thank you Oleg, This works perfectly!