Qlik Sense : Hide value on a map for small population

[Anonymous]

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.

[rubenmarin]

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)

[Anonymous]

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 😞

[rubenmarin]

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)

[Anonymous · Accepted solution]

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