Group By in Qlik Sense Frontend

[nickjose7]

serj_shu‌ kush141087‌ petter-s‌ mdmukramali‌ antoniotiman‌

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

[sunny_talwar · Accepted solution]

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

Sum(Aggr(YourExpression, Type, Group))

[rubenmarin]

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

[nickjose7]

Thank you Sunny. This Worked.

I was using Aggr in a silly way.

[nickjose7]

Thank you Ruben. This is great.