Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Average help

Hi Experts,

Can any one please help me on below requirement.

I have a bar chart with Month as dimension and Sum(Sales) as measure. But these sales are YTD values so for getting each month sales I have used below expression for calculation 2017 Sales.

I have calculated 2017 Sales by using below expression getting the result on this bar chart.

RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))


Requirement:

In a Reference line I have to show avg of 2017 Sales. Please help me how to write the avg of 2017 Sales expression.



Thanks in advance



5 Replies
Highlighted
Partner
Partner

Re: Average help

maybe this:

Avg( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 1000000

i hope it helps.

Highlighted
Creator II
Creator II

Re: Average help

Hi Andrea,

Thanks for your reply.

I am getting 0.09M in the avg reference line by the above expression.

I think I need to get 5.04 as average.

I have calculated like below in manual process

sum of all the months/12=4.87+5.66+4.73+4.96+4.15+5.94+6.24+5.71+6.07+6.38+ 5.88/12

=60.59/12=5.04

Please let me know is this correct process to validate the results.

Highlighted
Partner
Partner

Re: Average help

ok so let's try the below expression:

Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 12

Highlighted
Creator II
Creator II

Re: Average help

Hi Andrea,

Still not working. I am getting 35M as average.

When its Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 12 then it will take ytd Values but the above chart bar values as individual month values from YTD values those calculated by the below expression.


RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))

Highlighted
Partner
Partner

Re: Average help

what about the below expression ?


=Sum(

Aggr(

(

Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month >} Sales ) / 1000000,

-

Above( Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month >} Sales ) / 1000000 )

)

, Month )

)

/12