topic Re: Ignore single filter in AGGR function in QlikView

Ignore single filter in AGGR function

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

For an application, I need to ignore a certain search filter using Set Analysis into an aggregate function.

The AGGR function is:

=sum(aggr(_MachineInvoicePrice, [Machine Serial]))

The search filter [Claim Code] should not affect this calculation.

I did try the following solution without any result:

Sum(aggr({<[Claim Code]=>} _MachineInvoicePrice, [Machine Serial]))

But still, when I select the claim code, I see that the total of machine invoice prices changes.

How do I make sure the [Claim Code] filter can be set to anything, without affecting the total aggregation?

Thank you in advance

Re: Ignore single filter in AGGR function

varshavig12 — Wed, 22 Jun 2016 09:42:19 GMT

Try this:

aggr(sum( {< [Claim Code] =>} [Machine Serial] ), _MachineInvoicePrice)

Re: Ignore single filter in AGGR function

Thu, 23 Jun 2016 13:54:13 GMT

Hi Varsha Vig,

Your formula does not work.

(I also tried switching [Machine Serial ] and _MachineInvoicePrice from position as that would make more sense)

In my dimension of the chart, I have for instance 'Countries'.

When I apply your formula, I only get a result back when there is only 1 record for that specific country. Otherwise the value is -.

Best regards,

Re: Ignore single filter in AGGR function

Anonymous — Thu, 23 Jun 2016 23:27:56 GMT

Hi Nisse

If you need to use [Countries] as well, you will need to include it in the field list of AGGR function. So your expression would be:

sum(aggr(sum( {< [Claim Code] =>} _MachineInvoicePrice),[Machine Serial],[Countries])

Please consider attaching a small sample app so we can understand your issue better.

As for the AGGR function, remember it creates a virtual runtime mini-table from the fields you provide so it will lose resolution for [Countries] if you don't specify the field in the field list

Re: Ignore single filter in AGGR function

varshavig12 — Fri, 24 Jun 2016 03:15:20 GMT

I'm really very sorry,

I copy and pasted in the wrong place.

Try this:

aggr(sum( {< [Claim Code] =>} _MachineInvoicePrice),[Machine Serial])

If still you are stuck,

post a sample and screentshot of the desired output.

Re: Ignore single filter in AGGR function

sunny_talwar — Fri, 24 Jun 2016 03:33:04 GMT

Try this:

=Sum({<[Claim Code]=>} Aggr(Only({<[Claim Code]=>}_MachineInvoicePrice), [Machine Serial]))

Re: Ignore single filter in AGGR function

Fri, 24 Jun 2016 06:48:48 GMT

Hi Sunny,

Your formula does the trick. Selecting the Claim Code now does not affect the machine invoice price.

Many thanks for sharing your knowledge.

Best regards,