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

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?

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

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

Re: Use Total Function With Average And Aggr

Hi Elie,

        Try using : Avg(total Aggr(expression))

Regards,

Nitin G.

Highlighted

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 ?