topic Re: Inconsistent values when using filters in QlikView

Inconsistent values when using filters

midwest — Wed, 16 Aug 2023 14:38:21 GMT

Hi,

I have a chart indicating the frequency of visits over 2021 and 2022, based on dashboards that we completed in previous years. When I am not using filters, the frequencies are incorrect, but when I am using filters, they are correct, even though the dimension is the year that the service occurred.

This screenshots above are incorrect. For 2021, 2 visits should have a frequency of 10, 3 visits should have a frequency of 3. For 2022, 1 visit should have a frequency of 3, 2 visits should have a frequency of 8, and 3 visits should have a frequency of 6. Every other frequency is correct.

When filtering for year (2021/2022), the frequencies are correct.

When using the dimension of year as a filter, the frequencies are correct.

Does anyone know why this is happening? I am trying to create a report that has a chart of each year, side by side (as it looks in the first screenshot) showing the correct frequencies.

Thank you,

Re: Inconsistent values when using filters

Oleg_Troyansky — Wed, 16 Aug 2023 14:49:24 GMT

Hi midwest,

If you'd like anyone to troubleshoot your charts, please post a sample document, or at least specify all the formulas that you use in your chart - Dimensions and Expressions. Otherwise, we wouldn't have a clue about the issue.

Re: Inconsistent values when using filters

midwest — Wed, 16 Aug 2023 15:30:19 GMT

Here are my expressions:

count(aggr(if( max(Visit_number)= 1, 1), DQ_Unique_number))

count(aggr(if( max(Visit_number)= 2, 1), DQ_Unique_number))

count(aggr(if( max(Visit_number)= 3, 1), DQ_Unique_number))

count(aggr(if( max(Visit_number)= 4, 1), DQ_Unique_number))

count(aggr(if( max(Visit_number)= 5, 1), DQ_Unique_number))

count(aggr(if( max(Visit_number)= 6, 1), DQ_Unique_number))

count(aggr(if( max(Visit_number)= 7, 1), DQ_Unique_number))

count(aggr(if( max(Visit_number)= 8, 1), DQ_Unique_number))

Re: Inconsistent values when using filters

Oleg_Troyansky — Wed, 16 Aug 2023 15:59:08 GMT

Well, if your chart has Year as a dimension, then your AGGR() functions should also include Year in the list of AGGR() dimensions.

However, there is a better way of achieving the same, using an aggregated dimension, like this:

Dimension:

AGGR(Max(Visit_number), Year, DQ_Unique_number)

Expression:

count(distinct DQ_Unique_number)

I'm teaching a similar solution in my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik.

Join us at the Masters Summit for Qlik in Orlando or in Dublin to learn advanced Set Analysis, scripting, data modeling, performance, visualizations, and more - delivered by some of the best Qlik experts in the world!