Use Total Function With Average And Aggr

[elie_issa]

Dear All,

Please help me to find a solution for the below :

I Need to get the average Ratio of last 26 weeks for each month and show it on a Line Chart Trend as shown in the below Screens.

Instead of showing 26.97% I need to show 26.03 % for May 2018 (average 26 weeks)

Also

Instead of showing 26.11% I need to show 25.65 % for Apr 2018 (average 26 weeks)

And the same for the other months.

Expression :

avg({<Month = , Year = , [Month Year] = , [Week Year] = >}

aggr($(vNationalizationRatioLast26Weeks),Week , [Month Year],[Business Unit]))

Regards.

[arulsettu]

can you share the expression stored in the variable possible with sample

[Anil_Babu_Samineni]

May be TOTAL keyword need to be added?

[elie_issa]

Yes I was thinking to Add the Total But it seems I am not adding it in the right place in my expression.

Can you please assist.

Thanks

[elie_issa]

The variable is too much complicated and calling many other variables.

All I can do now is to find a solution using this variable.

Thanks.

[arulsettu]

try to include the TOTAL in the variable

[Anil_Babu_Samineni]

Can you place entire expression with TOTAL which you used? So then we may validate where yours is correct or not?

[nitin_01]

Hi Elie,

        Try using : Avg(total Aggr(expression))

Regards,

Nitin G.

[sunny_talwar]

May be one of these

Avg(TOTAL Aggr(Avg({<Month, Year, [Month Year], [Week Year]>} Aggr($(vNationalizationRatioLast26Weeks), Week, [Month Year],[Business Unit])), [Month Year]))

or

Avg(TOTAL {<Month, Year, [Month Year], [Week Year]>} Aggr($(vNationalizationRatioLast26Weeks), [Month Year]))

[elie_issa]

Hey Sunny,

I tried the two above expressions and they are giving the same % for all Months.

Even though the % is not correct.

So any other suggestion ?