Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Capture 2 May.PNGcapture 1 Apr.PNG

Expression :

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

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

Regards.

1 Solution

Accepted Solutions
MVP
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
arulsettu
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?

elie_issa
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

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

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

nitin_01
Contributor

Re: Use Total Function With Average And Aggr

Hi Elie,

        Try using : Avg(total Aggr(expression))

Regards,

Nitin G.

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

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