topic Re: Set analysis field equals to dimension value in QlikView

Set analysis field equals to dimension value

Tue, 05 Apr 2016 13:04:02 GMT

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:

Dimensions				Expressions
CN Submission Key	CN Number	Old Part	New Part	Old Part Held Stock	New Part Held Stock
E14250-D1-L1-05/02/2016-15:21:57	E14250	63606-02	63606-03	836	899
E14250-D1-L1-05/02/2016-15:25:39	E14250	63606-02	63606-03	836	899
E14250-D1-L1-05/02/2016-16:42:42	E14250	63606-02	63606-03	836	899
E14250-D1-L1-11/02/2016-10:04:46	E14250	63606-02	63606-03	836	899
E14250-D1-L1-24/02/2016-12:28:57	E14250	63606-02	63606-03	836	899
E14250-D1-L1-01/03/2016-12:53:41	E14250	63606-02	63606-03	836	899
E15264C-D1-L1-05/02/2016-16:26:33	E15264C	19040-01	19040-03	87	318
E16025-D1-L1-17/02/2016-14:58:07	E16025	52582-03	52582-04	789	786
E16025-D1-L1-18/02/2016-14:17:08	E16025	52582-03	52582-04	789	786
S14035-D1-L1-05/02/2016-15:27:21	S14035	16335-03	16335-04	434	761
S14035-D1-L1-05/02/2016-15:27:42	S14035	16335-03	16335-04	434	761
S14035-D1-L1-05/02/2016-15:27:52	S14035	16335-03	16335-04	434	761
S15031-D1-L1-05/02/2016-15:24:49	S15031	S63550-03	63550-04	426	211

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

Re: Set analysis field equals to dimension value

MK_QSL — Tue, 05 Apr 2016 13:16:49 GMT

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

What is PartID here?

Re: Set analysis field equals to dimension value

Tue, 05 Apr 2016 13:32:50 GMT

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

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?

Re: Set analysis field equals to dimension value

swuehl — Tue, 05 Apr 2016 13:38:26 GMT

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.

Re: Set analysis field equals to dimension value

settu_periasamy — Tue, 05 Apr 2016 13:42:23 GMT

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

=sum(

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

StockQuantityOnHand))

Re: Set analysis field equals to dimension value

Oleg_Troyansky — Tue, 05 Apr 2016 13:48:30 GMT

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.

Re: Set analysis field equals to dimension value

Wed, 06 Apr 2016 08:57:54 GMT

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

Re: Set analysis field equals to dimension value

Wed, 06 Apr 2016 09:56:43 GMT

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!

Re: Set analysis field equals to dimension value

Anonymous — Thu, 26 May 2016 18:47:42 GMT

Can we use Set Analysis in Calculated Dimension?

Re: Set analysis field equals to dimension value

sunny_talwar — Thu, 26 May 2016 18:48:59 GMT

Yes we can

Re: Set analysis field equals to dimension value

Anonymous — Thu, 26 May 2016 18:53:51 GMT

I am using set expression in Expression and it works fine. But when i use the same as Calculated Dimension, it says "Error in Calculated Dimension''

Re: Set analysis field equals to dimension value

sunny_talwar — Thu, 26 May 2016 18:56:52 GMT

What are you trying? Can you share? You might need to wrap it around with Aggr() function

Re: Set analysis field equals to dimension value

Anonymous — Thu, 26 May 2016 19:00:56 GMT

I can't post the exact Set Expression but here is the outline:

if(sum({$<Demand={'New'}>}(QUANTITY)) = sum({$<Demand={'New','Old'}>}(QUANTITY)),

'All' & ' ' & '(Provided' & ' ' & ((sum({$<Demand={'Old'}>}(QUANTITY))/sum({$<Demand={'New','Old'}>}(QUANTITY)))*100) & '%' & ')',

and two more if's similar to the one above.

Re: Set analysis field equals to dimension value

swuehl — Thu, 26 May 2016 19:01:07 GMT

That's exactely what you need to do when you want to use set analysis in a calculated dimension.

Set Analysis can only be applied to aggregation functions, and to be able to use aggregation functions in a dimension, you need to use advanced aggregation.

For example

=Aggr(Only({<Product = {A}>} Customer), Customer)

Re: Set analysis field equals to dimension value

sunny_talwar — Thu, 26 May 2016 19:10:45 GMT

What is the dimension you want to check the if statement on for your calculated dimension?

Aggr(if(sum({$<Demand={'New'}>}(QUANTITY)) = sum({$<Demand={'New','Old'}>}(QUANTITY)),

'All' & ' ' & '(Provided' & ' ' & ((sum({$<Demand={'Old'}>}(QUANTITY))/sum({$<Demand={'New','Old'}>}(QUANTITY)))*100) & '%' & ')', DimensionName)

Re: Set analysis field equals to dimension value

Anonymous — Thu, 26 May 2016 19:17:26 GMT

Sunny,

The purpose of this Set Analysis is to create a new Column. When I do this in Expressions, it works perfect. But I need to place this column with other Dimensions, not with Expressions.

Re: Set analysis field equals to dimension value

sunny_talwar — Thu, 26 May 2016 19:32:25 GMT

What were the dimension you used when you were using this as a dimension?

Re: Set analysis field equals to dimension value

Anonymous — Thu, 26 May 2016 19:35:10 GMT

There are many dimensions which are straight up Columns coming from database

Re: Set analysis field equals to dimension value

sunny_talwar — Thu, 26 May 2016 19:37:16 GMT

Can you try adding all of them where I have DimensionName separated by a comma (,):

Aggr(if(sum({$<Demand={'New'}>}(QUANTITY)) = sum({$<Demand={'New','Old'}>}(QUANTITY)),

'All' & ' ' & '(Provided' & ' ' & ((sum({$<Demand={'Old'}>}(QUANTITY))/sum({$<Demand={'New','Old'}>}(QUANTITY)))*100) & '%' & ')', DimensionName)

Also it would be better if you can start a new thread for your question. It is not nice for us to spam somebody else unfinished business.