How to use KPIs (calculations) as filter

[Anonymous]

Hi Guys,

I'm currently calculating some values, used as KPIs; in this case I have to count invoices with specific number of line items to create several categories. This works well, when setting it up as a KPI. But I would like to have this as a clickable filter; when clicking the KPI, I would like to filter the related invoices. This would allow me to understand the purchasing structure of my customers. Below you can see an example calculation:

COUNT(DISTINCT {<[Invoice ID] ={"=COUNT([Invoice row-nr])>2 and COUNT([Invoice row-nr])<=5 "}>} [Invoice ID])

Any idea how I could set this up? Or any work around available?

Thanks in advance!

Philipp

[juraj_misina]

Hello Philipp.

You could try using "=COUNT([Invoice row-nr])>2 and COUNT([Invoice row-nr])<=5" as a search to select Invoice ID's and then store that selection as a bookmark. Selecting that bookmark will perform search in Invoice ID field based on your criteria.

I've used this approach in QV, not sure if it works in Sense though.

Good luck!

Juraj

[Anonymous]

Dear Juraj,

Thanks for the idea; I can try that. Nevertheless, in the end I would also like to use the various categories (which are calculated) in further diagrams, like e.g. a pie chart; I would like to visualize the relation of the categories. Hence working with bookmarks wouldn't be the ideal solution.

Any further ideas? Thanks in advance!

Philipp 

[juraj_misina]

Try to precalculate those categories in script to create a new field like "Invoice Size Category", then you can use that field both dimension and filter. Or, if your "bucket" size is always the same, you can use Class() function to create calculated dimension, wich will work as a filter as well.

PS: I've just tried storing a search as a bookmark in Qlik Sense and it works.

[juraj_misina]

Hello Philipp,

any luck in creating the filters? Did you find a solution for this?

J