Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
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
Highlighted
MVP
MVP

Re: Set analysis field equals to dimension value

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

What is PartID here?

Highlighted
Not applicable

Re: Set analysis field equals to dimension value

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?

Highlighted
MVP
MVP

Re: Set analysis field equals to dimension value

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.

Highlighted

Re: Set analysis field equals to dimension value

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

=sum(

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

               StockQuantityOnHand))

Highlighted
MVP & Luminary
MVP & Luminary

Re: Set analysis field equals to dimension value

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.

Highlighted
Not applicable

Re: Set analysis field equals to dimension value

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

Highlighted
Not applicable

Re: Set analysis field equals to dimension value

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!

Highlighted
drj12345
New Contributor III

Re: Set analysis field equals to dimension value

Can we use Set Analysis in Calculated Dimension?

Highlighted

Re: Set analysis field equals to dimension value

Yes we can