Qlik Community

Qlik Sense App Development

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

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
ajmerajatin
New 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

6 Replies
Highlighted
Partner
Partner

Re: exception aggregation

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

Highlighted
ajmerajatin
New Contributor II

Re: exception aggregation

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

 

 

 

Highlighted
Partner
Partner

Re: exception aggregation

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: exception aggregation

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

Highlighted
ajmerajatin
New Contributor II

Re: exception aggregation

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: exception aggregation

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