topic Re: Aggr based on max values from two fields in QlikView

Aggr based on max values from two fields

felcar2013 — Wed, 12 Apr 2017 14:59:48 GMT

Hi community,

i have to get invoice amounts for serverals campaigns and invoices. the amounts are reapeated (see attachment), per campaign, Action, variant, target Group, etc. , but i Need to get the value of the amount of the invoice for which the following conditions are met:

1. Select MAX target_group_id field

2. f there are many MAX target_group_id s, then i need to select the MAX values of the tn_id field

Ex. invoice number 96772714 has only one target_group_id, so i select it and Aggregate, then i Need to select the max value of the tn_id field, which is 1765901050 and set the field B with that value, insted of on the first row.

How could i get the max of field one and then of the second field, if there is no absolute Maximum value?

thanks for any ideas, i attached the qv file

bon_id

tn_id

target_group_id

max target group id

aggr_sales per transaction

9672717	1765776724	12226	12226	105 €
	1765776725	12226	12226	0 €
	1765901049	12226	12226	0 €
	1765901050	12226	12226	0 €	105 €

Re: Aggr based on max values from two fields

sunny_talwar — Wed, 12 Apr 2017 15:16:05 GMT

May be this

If(tn_id = Max(TOTAL <[campaign-nr], [action-nr], [variant-nr], variant, [target-group-nr], [target-group], bon_id> tn_id),

Max(Aggr(Sum(sales),[campaign-nr],[action-nr],[variant-nr],variant,[target-group-nr],[target-group],target_group_id,bon_id,tn_id,voucher_id)))

Re: Aggr based on max values from two fields

felcar2013 — Wed, 12 Apr 2017 15:56:15 GMT

hi, thanks very much, this is very helpful, but i Need to consider the invoice amount of the invoice number (bon id) with first the max target Group id.

In case of the bon id 9672714, you find it in two different variants and we sum twice the same amount, what it should not be. i Need to set the amount in the MAX target Group id row, before checking the tn_id. How is that possible? this is an Aggregation at bon id and target Group Level first

Re: Aggr based on max values from two fields

sunny_talwar — Wed, 12 Apr 2017 16:03:45 GMT

May be this

If((target_group_id + tn_id/1E10) = Max(TOTAL <[campaign-nr], [action-nr], bon_id> (target_group_id + tn_id/1E10)),

Max(Aggr(Sum(sales),[campaign-nr],[action-nr],[variant-nr],variant,[target-group-nr],[target-group],target_group_id,bon_id,tn_id,voucher_id)))

Re: Aggr based on max values from two fields

felcar2013 — Wed, 12 Apr 2017 16:10:31 GMT

what is IE10?

Re: Aggr based on max values from two fields

sunny_talwar — Wed, 12 Apr 2017 16:11:10 GMT

Just another way of writing 10000000000

Re: Aggr based on max values from two fields

felcar2013 — Wed, 12 Apr 2017 16:18:38 GMT

really nice!

Re: Aggr based on max values from two fields

felcar2013 — Thu, 13 Apr 2017 07:59:31 GMT

when i take out the invoice number (bon id), tn_id and target Group Id, also, the granular data, i do not get the same number in the end. I shall get for the variant number 5 as Sub total 282 and for the variant number 7 as sub total 35

totalling 317