topic Re: Filter and set analysis problem in QlikView

Filter and set analysis problem

betty2 — Fri, 10 Mar 2023 08:46:05 GMT

Hello,

I want to calculate the number of users who have 2 medical records, to do this, I am using the following formula :

count({<user_id ={"=count(distinct[mr_id])=2"}>}DISTINCT user_id))

Then, I display the results by quarter as follows.

The issue is when I use a quarter filter and I select a quarter, the result changes, it is no longer the same value displayed in the table.

For example, in my filter I selected the 2022 – Q2.

And the result is :

I did the calculation manually, the correct result is 4 not 1.

Same for 2022-Q3 the result is 37 not 47

Is there a problem with my calculation? How can I solve this problem please?

Thank you in advance for your help.

Re: Filter and set analysis problem

henrikalmen — Fri, 10 Mar 2023 12:41:17 GMT

I get a bit confused here. When you select anything, the default function of Qlik is to only show data related to that selection, and a selection applies to all objects in all sheets of the application.

If you want to override that in a specific, so that a selection in a specific field doesn't affect the calculation, you can do that with a set expression. If the fields that should be be overridden are called MONTH and QUARTER you could do something like this:
count({$<MONTH=, QUARTER=>} distinct user_id)

In your example, you have a set expression and you can just add to it as in my example. But have you verified that your current set expression actually gives you the correct result when no selections are made?

Re: Filter and set analysis problem

marcus_sommer — Mon, 13 Mar 2023 14:34:26 GMT

It looked as if the association between the period-fields and the id-fields aren't ok. So you may need to check the data before trouble-shooting the expression. Quite useful for such a task is a table-box with the relevant fields and an unique record-identifier. If none exists you may create one with recno() and/or rowno() within the data-model. And then filtering your data - especially by your less valid records in regard to your mentioned proved filter and results it should become very obvious if the data are correct or not.

Beside this I would change the expression to:

count(DISTINCT user_id) * -(count(distinct [mr_id])=2)