Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
nickjose7
Creator
Creator

Group By in Qlik Sense Frontend

serj_shukush141087petter-smdmukramaliantoniotiman

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:

          

alphaActual betaRevised betaRevised alpha
Type GroupAppleBananaOrangesAppleBananaOrangesAppleBananaOrangesAppleBananaOranges
aqw             274                 6,010              52,617              478                 6,469              39,064              122 1,654 9,990              122                 2,681              23,475
cqw             192              22,710              78,688              218              94,018              74,374                 86                  36,943                  29,225                 86              10,132              35,107
sqw             363              12,689              27,479              346              93,535              28,088              162                  43,781                  13,147              162                 5,661              12,260
dqw             502                 2,863              80,829              200              37,016              18,845              224                  41,452                  21,103              224                 1,277              36,062
fqw             740              83,659              80,470                 29              89,794              17,979              330            1,022,263                204,683              330              37,325              35,902
rqw             164              73,114              59,121              186              81,350              54,295                 73                  32,001                  21,359                 73              32,620              26,377
eqw             781              72,626              72,624              221              65,614              26,124              348                103,452                  41,189              348              32,402              32,401
hqw             806              81,479              91,129              130              46,617              56,818              360                128,949                157,166              360              36,352              40,657
yqw             253                    198              33,320              200              48,297              31,849              113                  27,258                  17,975              113                       88              14,866
nqw             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

1 Solution

Accepted Solutions
sunny_talwar

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

Sum(Aggr(YourExpression, Type, Group))

View solution in original post

4 Replies
sunny_talwar

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
Creator
Creator
Author

Thank you Sunny. This Worked.

I was using Aggr in a silly way.

nickjose7
Creator
Creator
Author

Thank you Ruben. This is great.