topic Re: advanced aggregation in QlikView

advanced aggregation

felcar2013 — Wed, 19 Apr 2017 09:23:53 GMT

hi community,

in the table the amount of sales per invoice ID is shown (as invoice id). I Need to estimate only ONE amount of the sales per invoice Group by all dimensions, based on the following rule:

1. take the Maximum target Group id and the max customer tn id. This is already included in the Expression

the expected solution is a total of 317 €, as following

i) Variant id 5 : 282 €

ii) Variant id 7: 35 €

Problem:

I cannot get the total for the invoice ids : 9672717 of 105 € and of the invoice 9672715 of 20 €.

I have attached the table.

Columns:

i) total amount as column sales_aggr, but wrong because the condition in (1) is not fulfilled

ii) column sales_2 complies with the condition, but lacks to include the values where the # (customer tn id) > # (target Group id)

any help would be great,

thanks

Re: advanced aggregation

sunny_talwar — Wed, 19 Apr 2017 11:28:03 GMT

May be try this

Sum(Aggr(

If(Max(TOTAL <AKTIONSNUMMER, WABUCBON_ID> (ZG_ID+ TN_ID+GUT_ID)) = (ZG_ID+ TN_ID+GUT_ID),

Sum(Umsatz)), KAMPAGNENNUMMER,AKTIONSNUMMER,VARIANTENNUMMER,VARIANTE,ZIELGRUPPENNUMMER,ZIELGRUPPE,ZG_ID,WABUCBON_ID,TN_ID,GUT_ID))

Re: advanced aggregation

felcar2013 — Wed, 19 Apr 2017 11:31:10 GMT

hi, thanks,

not checked yet, but to sum the GUT_ID to the other fields is not necessarily correct, since i have more than 600 Million of the GUT ID.

Is there any other way to ignore the GUT_ID and take only the sum of any of them?

Re: advanced aggregation

sunny_talwar — Wed, 19 Apr 2017 11:35:11 GMT

Not sure if this is possible

Re: advanced aggregation

felcar2013 — Wed, 19 Apr 2017 11:37:18 GMT

thanks, the only Problem is, i don't know, how the data will come in the future

Re: advanced aggregation

sunny_talwar — Wed, 19 Apr 2017 11:44:20 GMT

Well you need to have some consistency in the data and you need to know what exactly you need to put out there.... Do you not care which GUI_ID displays the 105? out of the three under 1765901050 or do you want it to be shown against the max GUI_ID (252407612)?

Re: advanced aggregation

felcar2013 — Wed, 19 Apr 2017 11:49:25 GMT

important is only that i get the max ZG_ID + TN_ID. The GUT_ID value is not important. the number is Long, but it also could include strings, etc. Important is only that I Count the amount one, as well as other kpis, like customers, etc.

Re: advanced aggregation

sunny_talwar — Wed, 19 Apr 2017 11:53:05 GMT

You can check this out

Sum(Aggr(

If(Aggr(Max(TOTAL <AKTIONSNUMMER, WABUCBON_ID> (ZG_ID+ TN_ID)), KAMPAGNENNUMMER,AKTIONSNUMMER,VARIANTENNUMMER,VARIANTE,ZIELGRUPPENNUMMER,ZIELGRUPPE,ZG_ID,WABUCBON_ID,TN_ID) = (ZG_ID+ TN_ID),

Sum(Umsatz)), KAMPAGNENNUMMER,AKTIONSNUMMER,VARIANTENNUMMER,VARIANTE,ZIELGRUPPENNUMMER,ZIELGRUPPE,ZG_ID,WABUCBON_ID,TN_ID,GUT_ID))

Re: advanced aggregation

felcar2013 — Wed, 19 Apr 2017 11:57:59 GMT

much better, thanks!