topic Re: Set analysis - Ignore user selection of dimension in measure calculation, but apply it to the straight table in App Development

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

HarryLouis — Thu, 03 Mar 2022 15:41:00 GMT

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

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

Aurelien_Martinez — Thu, 03 Mar 2022 15:54:54 GMT

Hi,

You can try with P() expression, like :

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

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

ali_hijazi — Thu, 03 Mar 2022 17:59:57 GMT

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

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

HarryLouis — Sun, 06 Mar 2022 15:19:12 GMT

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.

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

ali_hijazi — Mon, 07 Mar 2022 07:48:49 GMT

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

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

HarryLouis — Mon, 07 Mar 2022 09:19:51 GMT

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!

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

HarryLouis — Mon, 07 Mar 2022 09:21:48 GMT

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:

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!

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

ali_hijazi — Mon, 07 Mar 2022 12:40:58 GMT

the use of

PN=P(PN)

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

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

HarryLouis — Mon, 07 Mar 2022 14:12:02 GMT

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.

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

ali_hijazi — Mon, 07 Mar 2022 14:58:46 GMT

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

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

HarryLouis — Mon, 07 Mar 2022 16:12:52 GMT

Thanks. I am a bit short on time this week to produce a sample QVF - also I have gotten to the required result now.

The reason why I applied the aggr( is because the total_rm_move_cost and repair order ID come from different tables with different hierarchies. Hence the Aggr() seemed to me the fastest and correct sollution for creating the summary by PN.