QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Partner

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

1 Solution

Accepted Solutions
MVP

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))

8 Replies
MVP

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))

Partner
Author

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?

MVP

Not sure if this is possible

Partner
Author

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

MVP

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)?

Partner
Author

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.

MVP

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))

Partner
Author

much better, thanks!