12 Replies Latest reply: Dec 13, 2017 11:09 AM by Sunny Talwar RSS

    Issue with counts and filters

    Ringo Keller

      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)

       

      1.png

       

      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')

      2.png

       

      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

          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
              Ringo Keller

              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
                  Sunny Talwar

                  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
                      Ringo Keller

                      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.


                      3.png


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

                        • Re: Issue with counts and filters
                          Jonathan Vitale

                          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

                              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
                                  Ringo Keller

                                  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

                                      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)

                        • Re: Issue with counts and filters
                          Jonathan Vitale

                          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
                              Ringo Keller

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

                                • Re: Issue with counts and filters
                                  Jonathan Vitale

                                  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
                                      Ringo Keller

                                      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..