# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (4)
1 Solution

Accepted Solutions
MVP

## Re: Use Total Function With Average And Aggr

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
Honored Contributor III

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

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)
Contributor II

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

Contributor II

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

Honored Contributor III

## Re: Use Total Function With Average And Aggr

try to include the TOTAL in the variable

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

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)
Highlighted
Contributor

## Re: Use Total Function With Average And Aggr

Hi Elie,

Try using : Avg(total Aggr(expression))

Regards,

Nitin G.

MVP

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

Contributor II

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