Set analysis field equals to dimension value

[#1]

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

[Anonymous]

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''

[sunny_talwar]

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

[Anonymous]

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.

[swuehl]

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)

[sunny_talwar]

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)

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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.