Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

Calculated dimension together with Expression containing set analysis on other dimensions

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!

1 Reply
Highlighted
MVP
MVP

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

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.