topic Re: Issue with counts and filters in QlikView

Issue with counts and filters

citizenk — Mon, 11 Dec 2017 12:15:15 GMT

Hello,

I am trying to create a statistic of linkages between IDs (PBGA_MASTER_ID) and vehicles (VEHICLE_ID) but at some point I lack understanding the relation between counts and set filters.e given below I want to have a summary of PBGA_MASTER_ID that are not having relation to a VEHICLE_ID (count<0 or NULL)

In this case I have a calculation of the vehicle count which works fine for both variants (as given in the boxes on the right):

count(distinct VEHICLE_ID)

count({1<PBGA_MASTER_ID=p(PBGA_MASTER_ID)>}distinct VEHICLE_ID)

In the box statistic I try to show all PBGA_MASTER_ID without linkage to a VEHICLE_ID in a table (and later in a bar chart) aggregated over months. But when I am selecting a filter (green arrow) the first count on the right side (mentioned above) drops to 0 and the second count remains since I am delimiting the considered filters to PBGA_MASTER_ID.

Now I am struggling because I see the PBGA_MASTER_ID 11178 in the box statistic as having no vehicles linked by the expression:

if(IsNull(VEHICLE_ID) or count(VEHICLE_ID)<1,'No Vehicles')

Now I am struggling because I see the PBGA_MASTER_ID 11178 in the box statistic as having no vehicles linked by the expression:

if(IsNull(VEHICLE_ID) or count(VEHICLE_ID)<1,'No Vehicles')

This means that somehow the calculation is affected by the filter but I need to know how to create an expression that covers all PBGA_MASTER_IDs with VEHICLE_ID either 0 or NULL but also regards the selected filters.

How can I make 11178 disappear from the box statistic by counting the VEHICLE_IDs correctly?

All alternate States are set to <inherited>

Thanks in advance for any tips

Re: Issue with counts and filters

sunny_talwar — Mon, 11 Dec 2017 16:26:03 GMT

If you want to make 11178 disappear, why not use count(distinct VEHICLE_ID) which was working the way you want? I think I am missing something, right? Why do you need to use this? count({1<PBGA_MASTER_ID=p(PBGA_MASTER_ID)>}distinct VEHICLE_ID)

Re: Issue with counts and filters

jonvitale — Mon, 11 Dec 2017 16:44:05 GMT

When you say, "How can I make 11178 disappear?", do you mean just the text at that particular cell of the table or the entire row?

Re: Issue with counts and filters

citizenk — Mon, 11 Dec 2017 18:07:18 GMT

count(distinct VEHICLE_ID) brings up that 11178 has 0 VEHICLE_IDs linked as soon as the filter is set (second screenshot) but this count is wrong.

I can only force the correct count of 212 VEHICLE_IDs by

count({1<PBGA_MASTER_ID=p(PBGA_MASTER_ID)>}distinct VEHICLE_ID)

but I need the correct count in the chart "statistics" which I don't get as soon as a filter is set. In the chart "statistics" I would expect it not to appear since I only want PBGA_MASTER_ID in there that really have no linkage to a VEHICLE_ID.

Re: Issue with counts and filters

citizenk — Mon, 11 Dec 2017 18:08:42 GMT

The whole line since 11178 has vehicles and therefore it shouldn't appear. 212 is the correct count.

Re: Issue with counts and filters

sunny_talwar — Mon, 11 Dec 2017 18:20:11 GMT

What if you do this

If(IsNull(VEHICLE_ID) or Count({1<PBGA_MASTER_ID=p(PBGA_MASTER_ID)>} DISTINCT VEHICLE_ID) < 1, 'No Vehicles')

Re: Issue with counts and filters

jonvitale — Mon, 11 Dec 2017 18:57:18 GMT

I'm still not quite sure what you need, but from what I can gather: you don't want to display rows on the table based upon some value (or lack thereof) of a measure calculated on the row's dimensional values. So, whenever, the measure is calculated to 0 (or null) you want to suppress the row of the table. Does this sound right?

One thing you could do is create a calculated dimension using the AGGR function, which will produce Null in those cases where there are no "vehicles". Then unselect, "Include null values" from the options of the dimension (I use Qlik Sense, but I assume the options are the same).

So something like:

Aggr(if(IsNull(VEHICLE_ID) or count(VEHICLE_ID)<1,Null(), count(distinct VEHICLE_iD)), PBGA_MASTER_ID)

This will produce a column with the counts of distinct vehicle ids or Null if there aren't any. If it's a dimension, you can suppress nulls.

Let me know if it works.

Jonathan

Re: Issue with counts and filters

citizenk — Tue, 12 Dec 2017 06:43:20 GMT

Sadly no positive effect. Even if I try to cut the expression down to

If(IsNull(VEHICLE_ID) or Count({1<PBGA_MASTER_ID=p(PBGA_MASTER_ID)>} DISTINCT VEHICLE_ID) < 1, 'No Vehicles')

11178 appears.

Maybe you have an idea and more insight if I explain what I finally want:

I want to have a bar chart that

aggregates the count of PBGA_MASTER_IDs without vehicle linkage
over months
regarding the filter set in the active sheet

But since e.g. 11178 always appears which actually has vehicle linkages I failed by now.

In the following picture you can see my desired output but the only value for Feb 2016 is 11178 which indeed has 212 vehicle linkages.

Maybe we can alter your suggested expression to make it work.

Re: Issue with counts and filters

citizenk — Tue, 12 Dec 2017 07:17:20 GMT

Sorry for being ambiguous but I want it the other way round. I only want to see the

PBGA_MASTER_ID where
- count(VEHICLE_ID) is NULL
- or
- isnull(VEHICLE_ID)

to use it in a bar chart (you can see a more detailed explanation in my last reply to Sunny Talwar). But strangely the PBGA_MASTER_ID 11178 pretends to have no VEHICLE_ID linkages as soon as a filter is set while before setting a filter it had 212 VEHICLE_IDs..

Re: Issue with counts and filters

jonvitale — Tue, 12 Dec 2017 14:12:08 GMT

Thanks for more information,

I'm sure that Sunny will give a non-AGGR response. But this might work:

You want an if-statement to determine if a master-id is linked to any vehicle ids. This aggr function will do that for each master_id.

Aggr(If (Count(distinct VEHICLE_ID) > 0, 1, 0), PBGA_MASTER_ID).

You need to wrap this virtual table from the Aggr function (master ids by distinct vehicle count) in an aggregation function, count.

Count(Aggr(If (Count(distinct VEHICLE_ID) > 0, 1, 0), PBGA_MASTER_ID))

(Note, I still am not quite sure what you need regarding user selections, but since I'm not specifying in set analysis it will take user selections.)

Re: Issue with counts and filters

sunny_talwar — Wed, 13 Dec 2017 13:20:43 GMT

I am not really sure what the OP is looking for to propose anything.... few things I can point out in your expressions are this...

Count(Aggr(If (Count(distinct VEHICLE_ID) > 0, 1, 0), PBGA_MASTER_ID))

This is probably not a right expression because if you are counting 1s and 0s, they will all be counted. You have two options to fix this

1) Count(Aggr(If (Count(distinct VEHICLE_ID) > 0, 1), PBGA_MASTER_ID)) -> Make false condition null so that count only counts 1

2) Sum(Aggr(If (Count(distinct VEHICLE_ID) > 0, 1, 0), PBGA_MASTER_ID)) -> Use Sum instead of Count because then 0s will still be summed to 0s and 1s will give you the number you might need.

Alternatively, doing this in set analysis might be another option

Count(DISTINCT {<PBGA_MASTER_ID = {"=Count(DISTINCT VEHICLE_ID) > 0"}>} PBGA_MASTER_ID)

This is doable because you were only aggregating over a single dimension in your Aggr() function... if this was not the case, then you would not have been able to use set analysis without some manipulation in the script.

Re: Issue with counts and filters

citizenk — Wed, 13 Dec 2017 15:56:39 GMT

First of all: Thanks to both of you for the great support so far. All of the mentioned expression from the last replies work and then again not. But let me explain:
I think I came a bit closer to the reason for this issue which admittedly needs a new approach but hopefully can be solved with a sophisticated expression:

PBGA_MASTER_ID has also linkages to vehicle manufacturers POE_OE_MAKE_NAME but in case a vehicle manufacturer is not linked to the PBGA_MASTER_ID a user can set a "research request" via the field POER_STATUS_ID and by that a specialised research team can add vehicle linkages for this manufacturer.

e.g.

PBGA_MASTER_ID: 11178

linked to the following manufacturers

POE_OE_MAKE_NAME count(VEHICLE_ID) POER_STATUS_ID

Toyota 100 NULL

Mercedes-Benz 112 NULL

SsangYong 0 RR (Research Request)

As we see SsangYong has no vehicle linkage but set the POER_STATUS_ID "RR" since it needs vehicle linkages.

When I am counting the VEHICLE_ID without filters set ("Clear State") Qlik counts the linked VEHICLE_IDs correctly (212 vehicles over 3 manufacturers).

In the moment I set a filter like POER_STATUS_ID= RR (Research Request) only SsangYong is considered since it has stored a POER_STATUS_ID against and it's according vehicle count is 0.

So this seems to reflect the SsangYong count (0) to the whole PBGA_MASTER_ID and I end up with PBGA_MASTER_ID 11178 when I am counting only PBGA_MASTER_ID with 0/NULL vehicle linkages.

This means I need an expression that allows me

to filter the PBGA_MASTER_IDs
where a POER_STATUS_ID is stored for any of it's POE_OE_MAKE_NAME
but the aggregated count of vehicles over the whole PBGA_MASTER_IDs is 0/NULL.

Hope that makes sense.

Re: Issue with counts and filters

sunny_talwar — Wed, 13 Dec 2017 16:09:38 GMT

May be all you need is to ignore selection in POER_STATUS_ID?

Try this:

Count(DISTINCT {<PBGA_MASTER_ID = {"=Count(DISTINCT {<POER_STATUS_ID>} VEHICLE_ID) = 0"}, POER_STATUS_ID>} PBGA_MASTER_ID)

or this

Count(DISTINCT {<PBGA_MASTER_ID = {"=Count(DISTINCT {<POER_STATUS_ID>} VEHICLE_ID) = 0"}>} PBGA_MASTER_ID)