Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below issue.
In the below table I have calculated CC1 measure using below expression.
RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
CC1 total is 167,947,700 but getting Auto total as 120,672,758
The requirement is I have to create the second table in the app with Name and Scope as dimension and CC1 as Measure but getting all wrong totals.
.
Please help me on this.
Thanks in advance.
Hi Mahitha, in second table you will need an aggr that does the operation by date and sums all values:
Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1, Date, Name, SCOPE))
In the first table you can choose 'Sum' instead of 'Automatic' for the Totals function (CC1 column properties, almost at the end)
Hi Mahitha,
The total CC1 gives you bad result because the total isn't the sum the different values found for CC1, but is recalculated on the total data.
It does :
RangeMax(0,Sum(Value)) +
Sum(Value) here =
48906566 |
RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
Sum(Item1) here =
16410074 |
Sum(Item2) here =
28795310 |
etc...
Then does RangeMax on Sum(Item1), Sum(Item2)... /5 + 0,1, here =
71766192,1 |
Then sum 48906566 and 71766192,1, which =
120672758 |
You should try aggregating by the choosen dimensions.
Hope it helps,
Marina
replace null and missing values with Zero '0" and Try
Hi Mahitha, in second table you will need an aggr that does the operation by date and sums all values:
Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1, Date, Name, SCOPE))
In the first table you can choose 'Sum' instead of 'Automatic' for the Totals function (CC1 column properties, almost at the end)