Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Creator II

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

1 Solution

Accepted Solutions
MVP

I suggest you to use The As-Of Table if you are able to make changes in the script. If not, then you will need Missing Manual - Above() and Below() functions to solve this. Set Analysis isn't going to work here....

14 Replies
Master III

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

May be TOTAL keyword need to be added?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator II
Author

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

Thanks

Creator II
Author

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.

Master III

try to include the TOTAL in the variable

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator

Hi Elie,

Try using : Avg(total Aggr(expression))

Regards,

Nitin G.

MVP

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

Creator II
Author

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 ?

Tags
Community Browser