Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis field equals to dimension value

Hi all,

I have often read that “Set analysis does not evaluate data per dimension but once per chart”. However I know that this is not always the case.


I have the following table where the first 4 fields are dimensions and the last 2 fields are expressions:

    

DimensionsExpressions
CN Submission KeyCN Number   Old PartNew Part   Old Part Held StockNew Part Held Stock
E14250-D1-L1-05/02/2016-15:21:57E1425063606-0263606-03836899
E14250-D1-L1-05/02/2016-15:25:39E1425063606-0263606-03836899
E14250-D1-L1-05/02/2016-16:42:42E1425063606-0263606-03836899
E14250-D1-L1-11/02/2016-10:04:46E1425063606-0263606-03836899
E14250-D1-L1-24/02/2016-12:28:57E1425063606-0263606-03836899
E14250-D1-L1-01/03/2016-12:53:41E1425063606-0263606-03836899
E15264C-D1-L1-05/02/2016-16:26:33E15264C19040-0119040-0387318
E16025-D1-L1-17/02/2016-14:58:07E1602552582-0352582-04789786
E16025-D1-L1-18/02/2016-14:17:08E1602552582-0352582-04789786
S14035-D1-L1-05/02/2016-15:27:21S1403516335-0316335-04434761
S14035-D1-L1-05/02/2016-15:27:42S1403516335-0316335-04434761
S14035-D1-L1-05/02/2016-15:27:52S1403516335-0316335-04434761
S15031-D1-L1-05/02/2016-15:24:49S15031S63550-0363550-04426211

For the two expressions in the table above I want to write a set analysis similar to the one below:


=sum({$<PartID= [Old Part] }, StockHeldFlag = {'Y'}>} StockQuantityOnHand)

And

=sum({$<PartID= [New Part] }, StockHeldFlag = {'Y'}>} StockQuantityOnHand)

So if the old part for a particular row is 63606-02, I want to mimic the following expression (but obviously for each and every row in the graph):

=sum({$<PartID= {‘63606-02’} , StockHeldFlag = {'Y'}>} StockQuantityOnHand)

Is there a way how one can simply refer to the value of the corresponding dimension in an expression with set analysis?

Thanks Matt

18 Replies
MK_QSL
MVP
MVP

Based on above table, what output you are expecting after set analysis?

What is PartID here?

Not applicable
Author

I have the following structure where the red circles indicated the relevant part relating my problem:

export.png

Basically the 'Old Part' values and the 'New Part' values of the 'ENG Part Details' table are all found in PartID of the Parts table.

From the set analysis I want to get the 'StockQuantityOnHand' where the PartID is equal to the value of the corresponding 'Old Part' dimension value  (and the second set analysis is the same but for the new part).

Does this answer your question?

swuehl
MVP
MVP

matthewvonbrockdorff wrote:

Hi all,

I have often read that “Set analysis does not evaluate data per dimension but once per chart”. However I know that this is not always the case.


Well, I believe Set Analysis is evaluated only once per chart, not per dimension line. Could you elaborate further where this is not the case?

You can get a per record / dimension value comparison when you choose an appropriate key value in the set expression, like

Sum({<KEY = {"=FieldA = FieldB"} >} Value)

but this key needs to be granular enough.

settu_periasamy
Master III
Master III

May be if you want row by row check, try like this..

=sum(

          if(PartID= ‘63606-02’ and StockHeldFlag = 'Y',

               StockQuantityOnHand))

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Matt,

based on everything I know about QlikView and Set Analysis, this is impossible to achieve in Set Analysis, precisely because of the premise you have started from - the Set Analysis condition is only performed once per chart.

I suppose you can solve the problem by creating meaningful associations between the PartID and the Old and the New values in your data model. This way, you can create a condition that could be verified once per chart and not once for every line.

Without any substantial data modelling work, the only way to achieve the desired is by replacing the Set Analysis by an IF() condition that can be sensitive to the Dimension values.

Cheers,

Oleg Troyansky

Check out my new book QlikView Your Business.

Not applicable
Author

Hi Oleg,

Thanks for your advice! I am thinking of restructuring the data model in order to do thing the proper way and for the data structure to be efficient.

Since I already tried to alter the data structure and found some difficulties I created a new post if you want to have a look

Data Model difficulty

Regards,

Matt

Not applicable
Author

Hi settu,

The following did work for me!

=

sum(

if(PartID = [Old Part] AND StockHeldFlag = 'Y', StockQuantityOnHand)

)

The only drawback is that this is not very efficient and take some time to load the straight table. However at least it works!

Anonymous
Not applicable
Author

Can we use Set Analysis in Calculated Dimension?

sunny_talwar

Yes we can