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

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.

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

MVP

## Re: Use Total Function With Average And Aggr

May be TOTAL keyword need to be added?

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.

Can you please assist.

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

MVP

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

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 ?