4 Replies Latest reply: Sep 5, 2017 2:39 PM by Nick Jose

# Group By in Qlik Sense Frontend

Hi,

Can somebody help me in calculating the below in QlikSense:

Required Output:

(a) Revised beta Banana = 1597160

Formula: Beta Banana / Beta Apple * Revised Beta Apple

Where Revised Beta Apple = Alpha Apple / Total of Alpha Apple * Total of Beta Apple

(b) Revised beta Oranges = 682002

Formula: Beta Oranges / Beta Apple * Revised Beta Apple

Where Revised Beta Apple = Alpha Apple / Total of Alpha Apple * Total of Beta Apple

Data:

 alpha Actual beta Revised beta Revised alpha Type Group Apple Banana Oranges Apple Banana Oranges Apple Banana Oranges Apple Banana Oranges a qw 274 6,010 52,617 478 6,469 39,064 122 1,654 9,990 122 2,681 23,475 c qw 192 22,710 78,688 218 94,018 74,374 86 36,943 29,225 86 10,132 35,107 s qw 363 12,689 27,479 346 93,535 28,088 162 43,781 13,147 162 5,661 12,260 d qw 502 2,863 80,829 200 37,016 18,845 224 41,452 21,103 224 1,277 36,062 f qw 740 83,659 80,470 29 89,794 17,979 330 1,022,263 204,683 330 37,325 35,902 r qw 164 73,114 59,121 186 81,350 54,295 73 32,001 21,359 73 32,620 26,377 e qw 781 72,626 72,624 221 65,614 26,124 348 103,452 41,189 348 32,402 32,401 h qw 806 81,479 91,129 130 46,617 56,818 360 128,949 157,166 360 36,352 40,657 y qw 253 198 33,320 200 48,297 31,849 113 27,258 17,975 113 88 14,866 n qw 939 27,615 54,205 229 87,135 90,830 419 159,406 166,166 419 12,320 24,184 5,014 382,963 630,482 2,237 649,845 438,266 2,237 1,597,160 682,002 2,237 170,859 281,290

The problem is when I calculate (a) I get the value of Beta Banana i.e. 649845. And when I add the dimension ''Type" in Table box and set the property "Totals" as Sum it gives me the perfect value i.e. 1597160.

But the same expression does not work in other objects like KPI or Text Object.

I think I am not able to properly Group By or something.

What I need is 'Type' and 'Group' wise totals as calculated in the last row of above table. Excel is also attached to understand the working and output.

Please understand that the attached data is only for one group while I have multiple groups.

Any quick help would be highly appreciated.

Thanks

Nick

• ###### Re: Group By in Qlik Sense Frontend

I don't know what your expressions are, but try this

Sum(Aggr(YourExpression, Type, Group))

• ###### Re: Group By in Qlik Sense Frontend

Thank you Sunny. This Worked.

I was using Aggr in a silly way.

• ###### Re: Group By in Qlik Sense Frontend

Hi Nick, the group by in front-end can be done using Aggr(Expression ,Dimension1, Dimension2...)

It will return a value for each combination of the dimensions so it needs to be included in an aggregation that does something with all those values to return a single value, ie:

Sum(Aggr([Beta Banana], Type, Group))

• ###### Re: Group By in Qlik Sense Frontend

Thank you Ruben. This is great.