Calculated dimension together with Expression containing set analysis on other dimensions

Tue, 06 May 2014 05:14:38 GMT

Hi.

I have a straight table like this:

Product_Category Product_ID Price

1 1 $5.00

1 2 $7.00

1 3 $9.00

2 4 $3.00

2 5 $2.00

3 6 $2.00

3 7 $5.00

3 8 $10.00

3 9 $8.00

(1) I want the table to show everything that the user has not selected. For example,

- if the user selects Product_ID = { 2, 3 }, the table should show records for Product_ID = { 1, 4, 5, 6, 7, 8, 9 }

- if the user selects Product_Category = { 1 }, the table should show records for Product_ID = { 4, 5, 6, 7, 8, 9 } (i.e. all records where category is not = 1)

- if the user selects Product_Category = { 1 } and Product_ID = { 2 } , the table should show records for Product_ID = { 1, 3, 4, 5, 6, 7, 8, 9 } (i.e. if both fields selected, everything except the intersection should remain.

--------------------------------------------------------------------------------------------------------------------------------

I have used this set analysis in my expression and it works (Note: Product_ID is unique - no 2 are the same):

= sum ( { $ < Product_ID = e(Product_ID) > } Price)

--------------------------------------------------------------------------------------------------------------------------------

However, now I also want to filter off (i.e. make sure the table does not show) records where Price is below a given level, which the user can input (say vPriceThreshold).

So if the user inputs vPriceThreshold = $6.00 and then selects Product_ID = { 8 }, the table should exclude records where Price < $6.00 and the records where Product_ID = 8. So, what remains in the table should be: Product_ID = { 2, 3, 9 }.

I tried:

= sum ( {$ < Product_ID = e(Product_ID) , Price = { ">=$(vPriceThreshold) " } > } Price )

But it did not work the way I want it to.

So I tried using a calculated dimension: if ( aggr (sum ( { $ < Price = { ">=$(vPriceThreshold) " } > } Price ) , Product_ID ) , Product_ID).

The problem is that my expression (now just: sum ( {$ < Product_ID = e(Product_ID)) contains the dimension Product_ID, which is no longer a dimesion (since I am using a calculated dimension) - and it does not work.

--------------------------------------------------------------------------------------------------------------------------------

Any way to make my table work the way I want?

Thanks so much!

Re: Calculated dimension together with Expression containing set analysis on other dimensions

tresB — Tue, 06 May 2014 05:20:38 GMT

Your set expression looks fine. However, you may try putting '=' before variable name like:

= sum ( {$ < Product_ID = e(Product_ID) , Price = { ">=$(=vPriceThreshold) " } > } Price )

If this does not work, try sharing your sample qvw.

topic Re: Calculated dimension together with Expression containing set analysis on other dimensions in QlikView

Calculated dimension together with Expression containing set analysis on other dimensions

Re: Calculated dimension together with Expression containing set analysis on other dimensions