help with expression - aggregation DIFFERENT MONTHS

[silvia_assuncao]

Hi!

Please could someone help me with this expression problem.

the aggregation was working perfectly, until I added the months as dimensions, and I'm not getting to rebuild the expression so that I'll have the right value each month.

total values by month should be as the first chart.

find the file attached here

[sunny_talwar]

May be one of the two options

Expression 1:

RangeSum(

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'-'}>} [Adjust Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'+'}>} [Adjust Amount]/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Discount Type] = {'C'}, [Discount Signal] = {'-'}>} [Discount Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Plan Type] = {'C'}, [Registry ID] = {'60'}>} [Plan Amount with Taxes]/100))

/

If(Dimensionality() = 2,  Count(TOTAL <[Unique Account ID], [Reference Month]> DISTINCT [Unique Resource ID]), 1)

Expression2:

RangeSum(

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'-'}>} [Adjust Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'+'}>} [Adjust Amount]/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Discount Type] = {'C'}, [Discount Signal] = {'-'}>} [Discount Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Plan Type] = {'C'}, [Registry ID] = {'60'}>} [Plan Amount with Taxes]/100))

/

If(Dimensionality() = 2,  Count(TOTAL <[Unique Account ID], [Reference Month]> DISTINCT {<[Unique Resource ID] = {"=Len(Trim([Unique Resource ID])) > 0"}>} [Unique Resource ID]), 1)

First one includes the null value in the count when finding the Unique Resource ID level amount, where as the second one exclude the null.

[silvia_assuncao]

The second one is the one that I need

but I need the lines in  which the [Unique Resouce ID]is not a numberic format to desapear. what can be done in the expression?

[silvia_assuncao]

The second one is the one that I need

but I need the lines in  which the [Unique Resouce ID]is not a numberic format to desapear. what can be done in the expression?

[sunny_talwar]

Try selection 'Suppress When Value Is Null' for your second dimensions

[silvia_assuncao]

it worked for the first item, but the second still remains.

The one below is the one which still remains:

[sunny_talwar · Accepted solution]

Try one more time now

[silvia_assuncao]

Great!!! You saved the day again!!

thanks

[sunny_talwar]

No problem at all

[silvia_assuncao]

Hi again!

Now I'm creating a new chart with the same values, but I've changed the dimention to COST CENTER, but I was not able to get how to change the aggr and have the right values within the lines.

could you please help me again?