Comparison on aggregation

[raxlifaxli]

Hi

I am new to qlikview and I have to compare 2 aggregated values to select table records. The follwoing dataset is given

Invoice No	Product	Keyfigure 1	Keyfigure 2
a	10	0	100
a	20	0	100
a	30	100	100
b	10	100	100
b	20	100	100

I would like to select those invoice numbers,which on an aggregated Level, have for all Keyfigures1 = 100 and Keyfigure 2 = 100 values only i.e. Invoice No  b only.

Does anyone can help me on a solution?

Thanks in advance for your help.

[martinpohl]

see attached

the other way is to do this in script so you can select orders.

Regards

[sunny_talwar]

What do you mean when you say at the "aggregated level"? Do you have multiple rows per Invoice No, per Product and you want to sum them all?

[raxlifaxli]

Hi

yes, there are multiple records per invoice - due to multiple products on an invoice. Per invoice -product combination you have 2 keyfigures.  However only those invoices where all the keyfigures on the Detail Level are = 100 should be valid for selection.  In the given example this should be invoice "b"

[sunny_talwar · Accepted solution]

So, not sure what dimensions are you using, but you can try something like this in your expression

{<[Invoice No] = {"=Only(Aggr(Sum([Keyfigure 1]), [Invoice No], Product)) = 100 and Only(Aggr(Sum([Keyfigure 2]), [Invoice No], Product)) = 100"}>}

[raxlifaxli]

Hi Martin

thank you for the quick response.  I cannot filter  in the Dimension part as I Need to result to calcualte a Ratio with another keyfigure.  So is there a ways to do this directly in the set analyis?

[raxlifaxli]

Thanks Sunny

this solution is working for what I need

Cheers