exception aggregation

[ajmerajatin]

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.

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

[pedrobergo]

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]

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]

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]

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