total filter but without the dimension in discovery.

Nicholas_Campbell
2 yrs ago
2replies
Answered

I have a discovery where I have a set of premium values by reference. I want to exclude any reference where the premium value is above a set value.

This is possible using the total filter which is fine, but now i only want to see the total premium that is not excluded ?

so for example I have 10 policies which total £1,000. i want to remove two because they are over £200. I can use the total filter to exclude anything where premium is over £200 so I now get say 8 policies, and a total of £500.

If I then only want to show the total i.e. £500 on a single line and not each individual reference, is that possible?

Thanks

Nick

- Senior Director of Product
- Ian_Macdonald
- 2 yrs ago
- Reported - view
Hi Nick,

Create a Dynamic List of your References, filtered by the Premium being greater than 200. You could parameterise this to allow dynamic changing of the filtering value if you want.

Then create an Aggregate of the Dynamic List to give your Total Line. You can then use the Total by itself or combine it with the dynamic List to see the line details.

Here's an example using Product Sub Categories (the Reference) from our training model.

Create the Dynamic List filtered by Sales > Parameter:

Note I am using the Greater Than sign and the checking the Exclude from List option.

Create an Aggregate of this list:

Put the List and The Aggregate into the Report and use the slider to choose the cut off value

Or just use the Total:

Done!

Hope that helps!

Ian
- Nicholas_Campbell
- 2 yrs ago
- Reported - view
Hi

Thanks Ian, this has saved me so much time.

Much appreciated :)