Ignore single filter in AGGR function

[#1]

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

[varshavig12]

Try this:

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

[#3]

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]

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]

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 · Accepted solution]

Try this:

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

[#7]

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,