Straight Table : Calculated Dimension, IF and Set Analysis

[MK_QSL]

I have below Sales Table

Sales:

Load * Inline

[

  Customer, Product, Sales

  AAA, P1, 100

  AAA, P2, 8000

  AAA, P3, 9000

  AAA, P4, 10000

  ABC, P2, 2000

  ABC, P3, 3000

  ABC, P4, 4000

  ACD, P2, 5000

  ACD, P3, 6000

  ACD, P4, 7000

  BFG, P1, 150

  BFG, P2, 200

  BFG, P3, 340

  BFG, P4, 310

];

Need a Straight Table for Sales with below condition

  Only For Customer starting with alphabet A and

  Only For Prodcut P2, P3 and P4

I have created two different tables using If, Calculated Dimension and Set Analysis

Table 1

Dimension

Customer

Product

Expression

SUM({<Customer = {'A*'}, Product = {'P2','P3','P4'}>}Sales)

But the problem is, now I can't filter data by clicking any cell of this straight table...

i.e. if user click on P2, it should filter Straight Table by Product P2 only....

same way if user click/select ABC, Straight Table should show sales of only ABC....

Table 2

Calculated Dimension

=IF(Customer like 'A*',Customer)

Calculated

=IF(Product = 'P2','P2',Product)

For both Suppress When When is Null Ticked

Expression

=SUM(IF(Product = 'P2' or Product = 'P3' or Product = 'P4', Sales))

Now here, I can click on any cell and the Straight Table is filtering data accordingly.....

Can someone briefly explain about this strange behaviour ?

[Gysbert_Wassenaar · Accepted solution]

Adding {<Customer = {'A*'}, Product = {'P2','P3','P4'}>} to an expression will fix the selections in Customer and Product to the values you specified. If you want to do additional filtering you should use {<Customer *= {'A*'}, Product *= {'P2','P3','P4'}>} so that the 'fixed' values (A*, P2, P3, P4) are intersected (that's what the *= does) with the selections you make in the default state. See attached example.

[ashfaq_haseeb]

Hi,

For this scenario set analysis is used to satisfy all two conditions as a mandatory event.

If you hard-code any condition with any field in set analysis, then this does not effect with any selection except hard-coded value.

Where as If condition satisfies the required step only and work for selection accordingly.

Regards

ASHFAQ