topic Conditional view of set analysis query in straight table in QlikView

Conditional view of set analysis query in straight table

Fri, 06 Sep 2013 10:05:55 GMT

Hi,

Please can someone help with my query?

I have a straight table in Alternate State 'Regular' with:

Dimension: Customer ID

Expressions: sum({[Analysis Group 1] * $}[Product Ordered]) , sum({[Analysis Group 2] * $}[Product Ordered])

Customer ID	sum({[Analysis Group 1] * $}[Product Ordered])	sum({[Analysis Group 2] * $}[Product Ordered])
1	100	50
2	0	80
3	60	0
4	50	75

I want to only diplay rows where sum({[Analysis Group 1] * $}[Product Ordered]) > 0 and sum({[Analysis Group 2] * $}[Product Ordered]) > 0 so my table looks like:

Customer ID	sum({[Analysis Group 1] * $}[Product Ordered])	sum({[Analysis Group 2] * $}[Product Ordered])
1	100	50
4	50	75

How do I do this please?

Thanks

Re: Conditional view of set analysis query in straight table

tresB — Fri, 06 Sep 2013 10:13:34 GMT

Copy your condition, goto presentation tab, Select the first expression under column, select 'Conditional', put the expression, select the second expression and put the same expression selecting conditional.

hope this helps

Re: Conditional view of set analysis query in straight table

Fri, 06 Sep 2013 10:33:32 GMT

Thanks. That works for each of my expressions separately, but not when I combine them. I need both sum({[Analysis Group 1] * $}[Product Ordered]) > 0 and sum({[Analysis Group 2] * $}[Product Ordered]) > 0 for the row to display. Any thoughts?

Re: Conditional view of set analysis query in straight table

simondachstr — Fri, 06 Sep 2013 10:45:55 GMT

sum({[Analysis Group 1] * $}[Product Ordered])*sum({[Analysis Group 2] * $}[Product Ordered])>0 maybe?

Re: Conditional view of set analysis query in straight table

Fri, 06 Sep 2013 10:54:37 GMT

No that doesn't work either. I am wondering whether I need to use a calculated dimension?

Re: Conditional view of set analysis query in straight table

tresB — Fri, 06 Sep 2013 11:43:51 GMT

Have you tried putting the combined expression as the conditional expression(presentation tab) for both of the expressions like:

=sum({[Analysis Group 1] * $}[Product Ordered]) > 0 and sum({[Analysis Group 2] * $}[Product Ordered]) > 0

Re: Re: Conditional view of set analysis query in straight table

Fri, 06 Sep 2013 12:11:49 GMT

Yes and unfortunately that didn't work.

I have however figured it out! I defined the expression as:

if(sum({[Analysis Group 1] * $}[Product Ordered])>0 and sum({[Analysis Group 2] * $}[Product Ordered])>0,sum({[Analysis Group 1] * $}[Product Ordered])

and similarly for sum({[Analysis Group 2] * $}[Product Ordered]).

I then ensured that suppress zero values and suppress missing were checked for my dimension and expressions.

Thanks for all your help.