topic Re: Dimension with a conditional sum in App Development

Dimension with a conditional sum

Shi — Sat, 16 Nov 2024 17:33:29 GMT

Hi to everyone!

I have an issue. I need to create a dimension MONTH that is dependent on two conditions. I want consider the dimension MONTH only if the Measure it is equal 'Volumes' and its sum on that dimension is = 11.

I tried to write the following expression:

if (Sum({$<Measure={'Volumes'}>}VALUE)=11, MONTH)

The expression seems to be right, but then the dimension is invalid.

I tried also :

if ([Measure]='Volumes' and VALUE=11, MESE_FISCALE)

that one seems to work in the dimension, but I want the aggregate sum of the VALUE not only the single value.

Thanks to everyone!

Re: Dimension with a conditional sum

edwin — Thu, 28 Jan 2021 18:36:47 GMT

the expression
if (Sum({$<Measure={'Volumes'}>}VALUE)=11, MONTH)
is not allowed in dimension, encapsulate your SUM with AGGR depending on your other dimensions you need to decide what dimensions go inside the AGGR. try aggr(Sum({$<Measure={'Volumes'}>}VALUE),MONTH)
also, validate first if the expression will return a value add Sum({$<Measure={'Volumes'}>}VALUE) as an expression first.

Re: Dimension with a conditional sum

Rodj — Fri, 29 Jan 2021 04:31:44 GMT

As @edwin has said, you can't use that kind of expression in a calculated dimension. 99 times out of a 100 when I see this sort of requirement I find it can be solved by having the correct set analysis in your measure expression. Is VALUE a static single value or are you truly trying to sum it? Can you share what measure you are trying to calculate?

Re: Dimension with a conditional sum

Shi — Fri, 29 Jan 2021 09:41:02 GMT

Thanks, with your suggestion I resolved my problem!

Re: Dimension with a conditional sum

edwin — Fri, 29 Jan 2021 17:01:38 GMT