6 Replies Latest reply: May 10, 2018 9:42 AM by Carlos Daniel

Sum with dimensions and sub dimensions

Hi,

I have the following sample of data:

 code Q1 Q1 OP Q1 VALUE F 46401606 Q106_7 Q106_7_1 0 55.55556 46401606 Q106_7 Q106_7_2 0 55.55556 46401606 Q106_7 Q106_7_3 1 55.55556 46401606 Q106_7 Q106_7_4 0 55.55556 46401606 Q106_7 Q106_7_5 0 55.55556 46401606 Q106_7 Q106_7_6 0 55.55556 46401615 Q106_7 Q106_7_1 1 47 46401615 Q106_7 Q106_7_2 0 47 46401615 Q106_7 Q106_7_3 0 47 46401615 Q106_7 Q106_7_4 0 47 46401615 Q106_7 Q106_7_5 1 47 46401615 Q106_7 Q106_7_6 0 47 46401628 Q106_7 Q106_7_1 0 41.46154 46401628 Q106_7 Q106_7_2 0 41.46154 46401628 Q106_7 Q106_7_3 1 41.46154 46401628 Q106_7 Q106_7_4 0 41.46154 46401628 Q106_7 Q106_7_5 0 41.46154 46401628 Q106_7 Q106_7_6 0 41.46154 46401632 Q106_7 Q106_7_1 1 191 46401632 Q106_7 Q106_7_2 0 191 46401632 Q106_7 Q106_7_3 0 191 46401632 Q106_7 Q106_7_4 0 191 46401632 Q106_7 Q106_7_5 0 191 46401632 Q106_7 Q106_7_6 0 191

If i set a bar chart with the dimension Q1 OP and the following metric:

sum({\$<Q1_VALUE={1}>} F) / sum(total {\$<Q1_VALUE={1,0}>} F)

The values that are displayed are correct, split by Q1 OP. But the same chart with the dimension Q1 shows incorrect values. Shouldn't the chart with dimension Q1 display the same values as Q1 OP but added up?

Thanks.

• Re: Sum with dimensions and sub dimensions

Not entirely sure I understand... but may be you need this

Sum({\$<Q1_VALUE={1}>} F) / Sum(TOTAL <Q1> {\$<Q1_VALUE={1,0}>} F)

• Re: Sum with dimensions and sub dimensions

Hi, i've tried that and it makes no difference to the values presented. I've blanked out the names of the categories, but the chart on the left should add the values of the chart on the right.

• Re: Sum with dimensions and sub dimensions

Question, you have changed the dimension from Q1 OP to Q1 or are you just adding Q1 to Q1 OP? If you replaced, then may be try this

Sum(Aggr(Sum({\$<Q1_VALUE={1}>} F) / Sum(TOTAL <Q1> {\$<Q1_VALUE={1,0}>} F), Q1, [Q1 OP]))

• Re: Sum with dimensions and sub dimensions

Hi,

That's it, thanks for your help. If it's not much to ask, can you explain the logic behind your thinking ?

• Re: Sum with dimensions and sub dimensions

Basically, you were only showing Q1, but you really wanted to perform the calculation at the Q1 OP level and then add it up... So, in order to do that you can use Aggr() function... which create a virtual table where there are two dimensions Q1 and Q1 OP and perform the calculation with both dimensions. And later Sum() function just sums up all the values of all Q1 OP within the Q1.

Second part, was the TOTAL qualifier. Here we needed to add <Q1> because when you performed the calculation... it seems that you only had selected a single Q1.... which is why you saw the total for Q106_7. But if nothing was selected... it would have showed overall total... in order to make the total for each Q1, I added TOTAL <Q1>.

• Re: Sum with dimensions and sub dimensions

Perfect. Thank you again.