14 Replies Latest reply: May 30, 2018 10:16 AM by Sunny Talwar

# Use Total Function With Average And Aggr

Dear All,

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] = >}

Regards.

• ###### Re: Use Total Function With Average And Aggr

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

• ###### Re: Use Total Function With Average And Aggr

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.

• ###### Re: Use Total Function With Average And Aggr

try to include the TOTAL in the variable

• ###### Re: Use Total Function With Average And Aggr

May be TOTAL keyword need to be added?

• ###### Re: Use Total Function With Average And Aggr

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

Thanks

• ###### Re: Use Total Function With Average And Aggr

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

• ###### Re: Use Total Function With Average And Aggr

Hi Elie,

Try using : Avg(total Aggr(expression))

Regards,

Nitin G.

• ###### Re: Use Total Function With Average And Aggr

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]))

• ###### Re: Use Total Function With Average And Aggr

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 ?

• ###### Re: Use Total Function With Average And Aggr

Will it be possible to share a sample to look at this?

• ###### Re: Use Total Function With Average And Aggr

This is the expression i am using :

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

The above expression is giving the correct % for May which is 26.03% but it showing the same percentage for all the other months :S

What should I add to the Expression To get the same result gotten for May for the other Months ????

• ###### Re: Use Total Function With Average And Aggr

But isn't that what you wanted?

• ###### Re: Use Total Function With Average And Aggr

No I want to get the Average of Last 26 weeks for May which is showing now 26.03 %

And for April 2018 it should show 25.65 % which is the average of the last 26 weeks from April 2018.

It's complicated I know hope it's Clear now.