Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

qdatainformatic
New Contributor II

Sum with dimensions and sub dimensions

Hi,

I have the following sample of data:

   

codeQ1Q1 OPQ1 VALUEF
46401606Q106_7Q106_7_1055.55556
46401606Q106_7Q106_7_2055.55556
46401606Q106_7Q106_7_3155.55556
46401606Q106_7Q106_7_4055.55556
46401606Q106_7Q106_7_5055.55556
46401606Q106_7Q106_7_6055.55556
46401615Q106_7Q106_7_1147
46401615Q106_7Q106_7_2047
46401615Q106_7Q106_7_3047
46401615Q106_7Q106_7_4047
46401615Q106_7Q106_7_5147
46401615Q106_7Q106_7_6047
46401628Q106_7Q106_7_1041.46154
46401628Q106_7Q106_7_2041.46154
46401628Q106_7Q106_7_3141.46154
46401628Q106_7Q106_7_4041.46154
46401628Q106_7Q106_7_5041.46154
46401628Q106_7Q106_7_6041.46154
46401632Q106_7Q106_7_11191
46401632Q106_7Q106_7_20191
46401632Q106_7Q106_7_30191
46401632Q106_7Q106_7_40191
46401632Q106_7Q106_7_50191
46401632Q106_7Q106_7_60191

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.

Tags (1)
1 Solution

Accepted Solutions

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]))

6 Replies

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)

qdatainformatic
New Contributor II

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.

2018_05_10_12_56_30_Qlik_Sense_Desktop.png

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]))

qdatainformatic
New Contributor II

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>.

qdatainformatic
New Contributor II

Re: Sum with dimensions and sub dimensions

Perfect. Thank you again.

Community Browser