Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem
MVP
MVP
‎2018-11-16 03:58 AM

Help with an expression containing aggr

Hi everybody ( @sunny_talwar )

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)

Capture.PNG

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)

Capture.PNG

 

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:

Capture.PNG

 

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 : 

Capture.PNG

 

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

Capture.PNG

 

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

avg( aggr( 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])

, DR))

,

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 !

873 Views
0 Likes
1 Solution

Accepted Solutions
5 Replies
OmarBenSalem
MVP
MVP
‎2018-11-16 04:44 AM
Author

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

see:

Capture.PNG

 

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

The second bar chart is now as follow:

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

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

3

))

 

I'll be changing the "1" with the expression of Annual avg:

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

avg( aggr( 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])

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

3

))

 

Result:

Capture.PNG

 

Now I'll try to add the Month Avg expression:

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

avg( aggr( 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])

, DR))
,
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))
,

3

))

 

Result:

Capture.PNGIt's  ONLY accepting the FIRST condition of the IF; if I change it as follow:

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

1
,
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))
,

3

))

 

It won't work:

 

Capture.PNG

 

But I put the Month Avg on the Annual part (first condition of the if ) , it works :

 

 

 

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

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))
,
if(ValueList('Annuel','Mensuel','Hebdomadaire')='Mensuel',
2
,

3

))

 

Result:

Capture.PNG

 

What am I doing wrong?

@sunny_talwar

862 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-11-16 07:20 AM

In response to OmarBenSalem

Try adding NODISTINCT after Aggr() in your complete if statement and see if that works

851 Views
sunny_talwar
MVP
MVP
‎2018-11-16 07:22 AM

In response to OmarBenSalem

Like this

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

avg( aggr( NODISTINCT 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])

, DR))

,

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

avg({<TDBH_CLI_DELEG_REG-={'NATIONAL'} >}
aggr( NODISTINCT
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( NODISTINCT 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))

))
849 Views
OmarBenSalem
MVP
MVP
‎2018-11-16 07:26 AM
Author

In response to sunny_talwar

It does work ! But the number are not the same 

Capture.PNG

 

1) Which are the correct numbers?

2) Why the hell it does work with nodistinct and does not work without?

848 Views
0 Likes