Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignore single filter in AGGR function

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

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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

View solution in original post

6 Replies
varshavig12
Specialist
Specialist

Try this:

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

Not applicable
Author

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,

Anonymous
Not applicable
Author

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

varshavig12
Specialist
Specialist

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.

sunny_talwar

Try this:

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

Not applicable
Author

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,