topic Re: Comparison on aggregation in QlikView

Comparison on aggregation

raxlifaxli — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Comparison on aggregation

martinpohl — Mon, 07 Oct 2019 12:22:28 GMT

see attached

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

Regards

Re: Comparison on aggregation

sunny_talwar — Mon, 07 Oct 2019 12:25:52 GMT

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?

Re: Comparison on aggregation

raxlifaxli — Mon, 07 Oct 2019 12:31:14 GMT

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"

Re: Comparison on aggregation

sunny_talwar — Mon, 07 Oct 2019 12:39:25 GMT

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"}>}

Re: Comparison on aggregation

raxlifaxli — Mon, 07 Oct 2019 12:39:53 GMT

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?

Re: Comparison on aggregation

raxlifaxli — Mon, 07 Oct 2019 13:31:42 GMT

Thanks Sunny

this solution is working for what I need

Cheers