Set analysis - Ignore user selection of dimension in measure calculation, but apply it to the straight table

[HarryLouis]

Hi all,

I hope anyone can help me out with getting the set analyis / total correct on this. I have a table in my data model like below (simplified):

Part	raw material	cost
A	aa	5
A	bb	5
B	aa	10
C	cc	20

I am trying to output a table which sums the cost values of the raw material by Part:

PN	total_cost
A	10
B	10
C	20

This can of course be achieved by using the function Sum (cost). However when applying a selection to the raw material (e.g. raw material = aa), the user expects the result in the bottom table to remain the same.

To do this, I would apply sum( {<rm_pn=>} cost). Although it provides the expected outcome (e.g. 10 for A and 10 for B), it will still show C with a total_cost of 0.

The expected output is that the applied selection of raw material is disregarded in the calculation of total cost, but the table shown will only still show PN A and B.

Would really appreciate very much if anyone can help!

Thanks,

Harry

[Aurelien_Martinez]

Hi,

You can try with P() expression, like :

Sum({<[raw material]=, Part=P(Part)>} cost)

[ali_hijazi]

if you want to keep the table as is regardless of any selection, then write the following expression:
sum({1}Cost); this will make your table static with original result

[HarryLouis]

Thanks, but I had already tried this solution. The issue with this is that when a selection is applied to Raw material, the result set for the bottom table will still show all possible values.

The goal when applying a filter to [Raw material] is that the calculation for total_cost remains the same, but only the [PN] in which the [Raw material] occurs should remain visible in the bottom table.

[ali_hijazi]

Hello @HarryLouis 
did you try the following:
Sum({1<Part=p(Part)>}Cost)
I selected here the aa raw material and I got in the table A -> 10 and B->10
is this what you want to accomplish?
kindly advise

[HarryLouis]

Hi,

Using the below suggestion and the proposal below, I have gotten close to the answer. My actual formula used is a bit more complex:

sum({<rm_pn=,PN=p(PN)>}
	aggr(
		if([costing_method]='Consumption / purchase cost', 
		Sum( {<rm_pn=,PN=P(PN)>}total_rm_move_cost) * sum( {<rm_pn=,PN=P(PN)>}relative_usage) / count( {<rm_pn=,PN=P(PN)>} Distinct [Repair Order ID]),
		sum({<rm_pn=,PN=P(PN)>}fin_ratio) * sum({<rm_pn=,PN=P(PN)>}coverage_price)),company,PN,rm_pn))

Applying a selection  to rm_pn still has an impact in the example that I am following. My bottom straight table also has a company dimension in it. Could this be the reason and should it be added?

Thanks!

[HarryLouis]

Hi,

I think using the 1 does not seem to work (at least I think). Using the above suggestion and the proposal below, I have gotten close to the answer. My actual formula used is a bit more complex:

sum({<rm_pn=,PN=p(PN)>}
	aggr(
		if([costing_method]='Consumption / purchase cost', 
		Sum( {<rm_pn=,PN=P(PN)>}total_rm_move_cost) * sum( {<rm_pn=,PN=P(PN)>}relative_usage) / count( {<rm_pn=,PN=P(PN)>} Distinct [Repair Order ID]),
		sum({<rm_pn=,PN=P(PN)>}fin_ratio) * sum({<rm_pn=,PN=P(PN)>}coverage_price)),company,PN,rm_pn))

Applying a selection  to rm_pn still has an impact in the example that I am following using the formula above.

My bottom straight table also has a company dimension in it. Could this be the reason and should it be added?

Thanks!

[ali_hijazi]

the use of 

PN=P(PN)

will have no effect because you are working in the default set of data ($) 

[HarryLouis]

Hm you are right. I am getting to the expected result however when I'm applying  the below:

sum( {<rm_pn=,PN=p(PN),costing_method={'Consumption / purchase cost'}>} aggr(Sum({<rm_pn=,PN=p(PN)>}total_rm_move_cost) * sum({<rm_pn=,PN=P(PN)>}relative_usage) / count(total <PN>{<rm_pn=,PN=P(PN)>} Distinct [Repair Order ID]),company,PN,rm_pn))+
sum( {<rm_pn=,PN=p(PN),costing_method-={'Consumption / purchase cost'}>} aggr(sum({<rm_pn=,PN=P(PN)>}fin_ratio) * sum({<rm_pn=,PN=P(PN)>}coverage_price),company,PN,rm_pn)))

 Where the if statement is moved inside the set analysis.

Removing the PN=p(PN) should I guess not change that result.

[ali_hijazi]

yes removing PN=P(PN) will not change the result
a question that I have: why are you using aggr function? what are the dimensions used in your table ?
if possible send me a sample QVF file with the expected result and I will try to solve it for you