Chart: Dynamic expression to show anothers dimension value

[marcelHesse]

Hi,

I'm facing a problem while trying to make our applications more dynamic.

In one chart we're trying to show the sum(Betrag) for all cost types and we'd like to compare these value to the sum(Betrag) of another cost type. But the compared cost type is dynamic.

working solution with compared cost type as additional dimension

In the example above, the compared cost type is added as an additional dimension. As you see, cost type "Handlingkosten" has the compared cost type "Handlingerlöse" und the value for "Betrag-Bezug" is 300 - the same as "Betrag" for  cost type "Handlingerlöse" - exactly what I need!

But we don't want that extra dimension - we only need the compared cost centers value.

In no way I'm able to get this working...

Attached you'll find an example file with the formulas of the working solution, as well as anotherone that is not working (even if I have every value I need)...

Thanks for your help!

Regards

Marcel

[agigliotti]

could you please explain better the logic behind your expected result?

[lironbaram · Accepted solution]

hi

this should do the trick for you 

aggr(NODISTINCT sum(if(%Kostenart = [Bezug-Kostenart],Betrag)),[Bezug-Kostenart])

but you can also use the table with the two dimensions , and just hide the second dimension 

you'll get the result you want 

[marcelHesse]

Hi Agigliotti,

thanks for your response.

I'd like to compare every cost type with one defined other cost type.

So in that example (see screenshot above) I need the value "Betrag" for the Dimension (%Kostenart) "Handlingerlöse" to be compared to the vaule "Betrag" for the Dimension (%Kostenart) "Handlingkosten". Both values shoud be show in dimension (%Kostenart) "Handlingkosten". 

The dimensions value for comparison can be found in "Bezug-Kostenart".

Hope that helps you understanding my problem...

Regards

Marcel

[Brett_Bleess]

Marcel, did Liron's suggestion work for you by chance?  If so, be sure to use the Accept as Solution button on his post to give him credit and let others know that did work.  Thought you may have missed that when you posted your last reply.

Regards,
Brett