Issue with counts and filters

[citizenk]

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

[sunny_talwar]

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.

[citizenk]

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.
 

[sunny_talwar · Accepted solution]

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)