How to calculate Avg on behalf of Current year and Last Year

[Shahzad_Ahsan]

Hi

I need to calculate Avg on Behalf of CY and LY like in below Table.

Year	Amount	Average
2016	5241	0
2017	5148	5194.5
2018	3658	4403
2019	7415	5536.5

The Avg of year 2017 is Sum of Amount of 2017 and 2016 divide by 2.

[madhu_r]

Hi,

Try this expression in script editor

If(len(Previous(Year))=0,0,(Amount+Previous(Amount))/2) as Average

[miskinmaz]

Hi,

You can try below expression:

if(rowno()<>1,
rangesum(above(Sum(Sales),0,2))/2)

[Shahzad_Ahsan]

Hi @madhu_r 

Your solution is working fine for this sample data. But for live data it is not working.

My fact table has Transaction_Date, Amount any many other fields and has millions of rows.

[madhu_r]

Hi Shahzad_Ahsan,

Can you share the sample of your live data

[jonathandienst · Accepted solution]

You could try this:

Alt((Sum(Amount) + Above(Sum(Amount)))/2, 0)

If you have more than one dimension, you need to add TOTAL to the Above() clause:

Alt((Sum(Amount) + Above(TOTAL Sum(Amount)))/2, 0)

The Alt() will cause the first value to be zero.

[Shahzad_Ahsan]

Hi @jonathandienst 

Thanks for your response. 

This is working fine. 

But I have one more question related to this.

Your expression is working fine in a Table. Suppose I have to show the current year avg as a KPI. then how is this possible??

[jonathandienst]

Please post this question as a new topic. You can refer back to this topic using the URL:

https://community.qlik.com/t5/forums/editpage/board-id/new-to-qlik-sense/message-id/133525