Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
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)
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 😞
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)
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...