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!
