topic Re: Straight Table : Calculated Dimension, IF and Set Analysis in QlikView

Straight Table : Calculated Dimension, IF and Set Analysis

MK_QSL — Wed, 29 Oct 2014 16:55:33 GMT

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 ?

Re: Straight Table : Calculated Dimension, IF and Set Analysis

Gysbert_Wassenaar — Wed, 29 Oct 2014 17:09:33 GMT

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.

Re: Straight Table : Calculated Dimension, IF and Set Analysis

ashfaq_haseeb — Wed, 29 Oct 2014 17:23:59 GMT

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