Aggr based on max values from two fields

[felcar2013]

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

B

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

[sunny_talwar]

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

[felcar2013]

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

[sunny_talwar]

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

[felcar2013]

what is IE10?

[sunny_talwar · Accepted solution]

Just another way of writing 10000000000

[felcar2013]

really nice!

[felcar2013]

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