4 Replies Latest reply: Apr 25, 2018 11:54 AM by nicolas sinquin RSS

    Qlik Sense : Hide value on a map for small population

    nicolas sinquin

      Hi,

       

      I've got a map and I want to hide some values when my population is too small.

       

      I  added a Variable extension (Qlik Branch). I defined TypeAnneeSelect as variable to choose Annee  (TypeAnneeSelect =1)or AnneeSCO (TypeAnneeSelect =2), and VAnneeSelect to get the year.

       

      My expression is :

       

      =Round(Avg(if ($(TypeAnneeSelect) = 1 ,

      if($(VAnneeSelect) = ANNEE_FH , NB_ENF_FH),

              if($(VAnneeSelect) = ANNEE_SCO_FH  , NB_ENF_FH )

              )

          )

         ,0.1)


      I've a table like this

      Annee Annee_SCO ID     NB_ENF_FH Departement

      2017     2017             99          4           56

      2017      2017           0          1              29

      2017      2017           1          2               44

      2017      2018           2          1               29

      2017      2017           3          2               44

       

       

      that works fine, but i would like not showing Departement 56 for Annee = 2017 because there only 1 record.

      I hope it's clear.


      Thanks for your help.


        • Re: Qlik Sense : Hide value on a map for small population
          Ruben Marin

          Hi Nicolas, maybe you only need to add the condition to count values:

          =Round(Avg(if ($(TypeAnneeSelect) = 1 ,

          if(Count(ID)>0, // ID or other field that can be used to count records

          if($(VAnneeSelect) = ANNEE_FH , NB_ENF_FH),

                  if($(VAnneeSelect) = ANNEE_SCO_FH  , NB_ENF_FH )

                  )

              ))

             ,0.1)

            • Re: Qlik Sense : Hide value on a map for small population
              nicolas sinquin

              Hi Ruben,

              thanks for your answer, It does not work either

              I've also tried some things llike that, but unsuccesfull

               

              I even try something like this (Quartier is the name of the division on my map) :

               

              Round(Avg(if ($(TypeAnneeSelect) = 1 ,

                   if($(VAnneeSelect) = ANNEE_FH AND aggr(Count(distinct ID_FAM_FH), [Quartier]) > 10, NB_ENF_FH),

                      if($(VAnneeSelect) = ANNEE_SCO_FH AND aggr(Count(distinct ID_FAM_FH), [Quartier]) > 10 , NB_ENF_FH ,0)

                      )

                  )

                 ,0.1)

               

              But doesnt work :-(

                • Re: Qlik Sense : Hide value on a map for small population
                  Ruben Marin

                  The aggr returns a value for each dimension value, maybe with:

                  Round(Avg(if ($(TypeAnneeSelect) = 1 ,

                       if($(VAnneeSelect) = ANNEE_FH AND Sum(aggr(Count(distinct ID_FAM_FH), [Quartier])) > 10, NB_ENF_FH),

                          if($(VAnneeSelect) = ANNEE_SCO_FH AND Sum(aggr(Count(distinct ID_FAM_FH), [Quartier])) > 10 , NB_ENF_FH ,0)

                          )

                      )

                     ,0.1)

                    • Re: Qlik Sense : Hide value on a map for small population
                      nicolas sinquin

                      Doing like that anwer at my first question (exclude the division less than 100 people), but not completly, the results are not good ...

                      (id_pol is the id for my name's area)

                       

                      =Round(Avg(if ($(TypeAnneeSelect) = 1 ,

                           if($(VAnneeSelect) = ANNEE_FH AND aggr(Count(distinct ID_FAM_ACT_FAM), ANNEE_FH,ID_POL_IN_FH) > 100, NB_ENF_FH),

                              if($(VAnneeSelect) = ANNEE_SCO_FH AND aggr(Count(distinct ID_FAM_ACT_FAM), ANNEE_SCO_FH,ID_POL_IN_FH) > 100 , NB_ENF_FH

                              )

                              )

                          )

                         ,0.1)

                       

                      I've got always the same results if y choose Avg, Sum, min, max, as if this expression returns only one value...