Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ajmerajatin
Contributor II
Contributor II

exception aggregation

Hello community,

I need your support in setting up exception aggregation for our apps. This is a standard functionality available in SAP.

Attached is the example for reference – Left side is the raw data available and the right side of the pic is the expected results.

 

exception aggr.JPG

I tried using this

– sum(aggr((QTY), [COLUMN C], [COLUMN D])) : the values inside the table is not correct but brings the correct Total

 – sum(aggr((QTY), [COLUMN A], [COLUMN C], [COLUMN D])) : the values inside the table is correct but brings the incorrect Total

 

Regards,

Jatin

Labels (3)
6 Replies
pedrobergo
Employee
Employee

Hi @ajmerajatin ,

Why you trying to use AGGR ?

Seems to me that you want a results with the totals by each Column and a Total of Columns. You can just create 3 tables, add the desired Column and a measure with SUM(QTY).

Tell me if it isn´t that,

Pedro

ajmerajatin
Contributor II
Contributor II
Author

Hi @pedrobergo ,

Thank you looking into this! Sum(QTY) will never give me the right results becoz of the following:

1. Sum of Column A/ B/ C will give me 35

2. Data model has many duplicates values which will add up to much more .. (cannot use SUM(DISTINCT .. as well)

For reference, please check out the link of what I am trying to achieve... https://blogs.sap.com/2017/08/28/understanding-exception-aggregation-via-examples/

Regards,

Jatin

 

 

 

pedrobergo
Employee
Employee

Hi @ajmerajatin 

So your question its quite clear for now, but i´m afraid that you should detailed more to solve a real problem.

At Qlik, to make a any tuple level calculation, we use the Set Analysis operators (i.e. Sum({<Qty={">500"}>} Qty) ). Look at links bellow. And you can combine Aggr it with Set Analysis.

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAn...

https://community.qlik.com/t5/New-to-QlikView/Learning-Set-analysis-by-practice/m-p/1339066

 

Regards,

Pedro

marcus_sommer

I'm not really sure what do you want to output but you may need here and there a TOTAL and maybe also DISTINCT and to simplify the whole approach also a query to the dimensionality of the dimension-values (I don't want to say that's not possible to include everything within a single calculation but it may be more complex as necessary). This means to use an approach like:

if(dimensionality() > 0, sum(distinct QTY),
   sum(distinct aggr(sum(QTY), [COLUMN A])))

- Marcus

ajmerajatin
Contributor II
Contributor II
Author

Hi @pedrobergo , @marcus_sommer 

I think I tried the same earlier using this but no luck...

– sum(aggr((QTY), [COLUMN C], [COLUMN D])) : the values inside the table is not correct but brings the correct Total

 – sum(aggr((QTY), [COLUMN A], [COLUMN C], [COLUMN D])) : the values inside the table is correct but brings the incorrect Total

Already checked many links but unable to proceed or come up with a solution... 😞

marcus_sommer

If both approaches work for a certain part of the data/view it should also work within my above suggestion:

if(dimensionality() > 0,
   sum(aggr((QTY), [COLUMN A], [COLUMN C], [COLUMN D])),
   sum(aggr((QTY), [COLUMN C], [COLUMN D])))

Depending of your real charts and kind of exceptions you may need some further queries to the dimensionality and also adjusting the used calculations within all these branches. If your objects are really more complex I suggest to dive a bit deeper into the logic of aggr() and TOTAL/DISTINCT to avoid to develop more complicated solutions as necessary and also to ensure a reasonably performance (nested if-constructs with aggr will require much resources).

Beside this I suggest to consider to do at least some parts of it within the script (maybe also with adjustments to the whole datamodel).

- Marcus