topic Re: Help with an expression containing aggr in QlikView

Help with an expression containing aggr

OmarBenSalem — Fri, 16 Nov 2018 08:59:49 GMT

I have an issue with an expression and really hope someone would have an explanation / solution for it.

Here's it (it's a long message, but bare it with me plz)

I have 3 expressions : Year Avg, Month Avg and Week Avg

Here's an exp of one of the expressions (the Year one)

Original one:

Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_SEMAINE,TDBH_SRV_NIV1={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"<=$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))"},
TDBH_CAL_ANNEE={"$(=Max(TDBH_CAL_ANNEE))"},TDBH_YEARWEEK_FIELD>} TDBH_SRV_VALOECO_REEL_MSP)/
sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_SEMAINE,[Target_Services.Niveau 1]={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"<=$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))"},
TDBH_CAL_ANNEE={"$(=Max(TDBH_CAL_ANNEE))"},TDBH_YEARWEEK_FIELD>} [Target_Services.Valo Eco aux temps réels MSP])

With this, I'd have this result (see the total; it's wrong)

So, what I did is that I've added an aggr on the DR (the dimension) and then surrounded the whole with avg

The new expression is then as follow:

Avg(Aggr(OriginalExpression),DR))

Result (correct)

Till now, everything is just FINE;

As I was saying I have 3 expressions;

what I was doing is showing the 3 in one bar chart, using valuelist()

Here's how it was :

Dimension : ValueList('Annuel','Mensuel','Hebdomadaire')

Expression: if(ValueList('Annuel','Mensuel','Hebdomadaire')='Annuel',

sum(exp1),

if(ValueList('Annuel','Mensuel','Hebdomadaire')='Mensuel',

sum(exp2),

sum(exp3)

))

=> The bar chart is constructed with the 3 bars:

But, when I do transform the 3 expressiosnas shown above (adding the avg aggr part); it does not work !!

each expression is shown at is own :

But when I try to combine the 3 of them , only one is shown, as if I don't know how to write an if statement anymore...

Here's the original expression :

=if(ValueList('Annuel','Mensuel','Hebdomadaire')='Annuel',
Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_SEMAINE,TDBH_SRV_NIV1={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"<=$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))"},
TDBH_CAL_ANNEE={"$(=Max(TDBH_CAL_ANNEE))"},TDBH_YEARWEEK_FIELD>} TDBH_SRV_VALOECO_REEL_MSP)/
sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_SEMAINE,[Target_Services.Niveau 1]={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"<=$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))"},
TDBH_CAL_ANNEE={"$(=Max(TDBH_CAL_ANNEE))"},TDBH_YEARWEEK_FIELD>} [Target_Services.Valo Eco aux temps réels MSP])
,
if(ValueList('Annuel','Mensuel','Hebdomadaire')='Mensuel',

Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_SRV_NIV1={"*cadres*"},TDBH_YEARWEEK_FIELD,TDBH_CAL_SEMAINE,TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},TDBH_CAL_MOIS=p({<TDBH_CAL_MOIS={"$(=max({<TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'},TDBH_CAL_SEMAINE_TDB={'$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))'}>} TDBH_CAL_MOIS) )"}>} TDBH_CAL_MOIS)>}TDBH_SRV_VALOECO_REEL_MSP)
/Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},[Target_Services.Niveau 1]={"*cadres*"},TDBH_YEARWEEK_FIELD,TDBH_CAL_SEMAINE,TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},TDBH_CAL_MOIS=p({<TDBH_CAL_MOIS={"$(=max({<TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'},TDBH_CAL_SEMAINE_TDB={'$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))'}>} TDBH_CAL_MOIS))"}>} TDBH_CAL_MOIS)>}[Target_Services.Valo Eco aux temps réels MSP]),

if(ValueList('Annuel','Mensuel','Hebdomadaire')='Hebdomadaire',

Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},TDBH_SRV_NIV1={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"$(=max({<FlagService={1},TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'}>}TDBH_CAL_SEMAINE1))"}>}TDBH_SRV_VALOECO_REEL_MSP)
/Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},[Target_Services.Niveau 1]={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"$(=max({<FlagService={1},TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'}>}TDBH_CAL_SEMAINE1))"} >}[Target_Services.Valo Eco aux temps réels MSP])

)))

and here's the new one:

if(ValueList('Annuel','Mensuel','Hebdomadaire')='Annuel',

if(ValueList('Annuel','Mensuel','Hebdomadaire')='Mensuel',

avg({<TDBH_CLI_DELEG_REG-={'NATIONAL'} >}
aggr(
Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_SRV_NIV1={"*cadres*"},TDBH_YEARWEEK_FIELD,TDBH_CAL_SEMAINE,TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},TDBH_CAL_MOIS=p({<TDBH_CAL_MOIS={"$(=max({<TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'},TDBH_CAL_SEMAINE_TDB={'$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))'}>} TDBH_CAL_MOIS) )"}>} TDBH_CAL_MOIS)>}TDBH_SRV_VALOECO_REEL_MSP)
/Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},[Target_Services.Niveau 1]={"*cadres*"},TDBH_YEARWEEK_FIELD,TDBH_CAL_SEMAINE,TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},TDBH_CAL_MOIS=p({<TDBH_CAL_MOIS={"$(=max({<TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'},TDBH_CAL_SEMAINE_TDB={'$(=max({<FlagService={1} ,TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'} >}TDBH_CAL_SEMAINE1))'}>} TDBH_CAL_MOIS))"}>} TDBH_CAL_MOIS)>}[Target_Services.Valo Eco aux temps réels MSP]),DR))

avg(aggr(Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},TDBH_SRV_NIV1={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"$(=max({<FlagService={1},TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'}>}TDBH_CAL_SEMAINE1))"}>}TDBH_SRV_VALOECO_REEL_MSP)
/Sum({<TDBH_CLI_DELEG_REG-={'NATIONAL'},TDBH_CAL_ANNEE={"$(=max(TDBH_CAL_ANNEE))"},[Target_Services.Niveau 1]={"*cadres*"},TDBH_CAL_SEMAINE_TDB={"$(=max({<FlagService={1},TDBH_CAL_ANNEE={'$(=max(TDBH_CAL_ANNEE))'}>}TDBH_CAL_SEMAINE1))"} >}[Target_Services.Valo Eco aux temps réels MSP]),DR))

))

Please help !

Re: Help with an expression containing aggr

OmarBenSalem — Fri, 16 Nov 2018 09:44:00 GMT

What's really frustrating me, is that it only accept ONE new expression in the bar chart :

see:

The 3 KPI are the new expressions I want to add to the second bar chart:

The second bar chart is now as follow: