How to calculate average price in previous year?

Anonymous — Fri, 26 Aug 2016 22:04:24 GMT

Hello, Guys

I am new in Qlik Sense, and I am confused for a problem as below.

for exapple, I have one table [purchase orders].

Purchase Order No	Price	Quantity	Date	Amount	Material No	Company	Vendor No
8004009145	24.00	10	13/8/2016	240.00	Computer009	Fussery	34785
8004009146	12.00	10	13/8/2016	120.00	Mouse009	DuckPrint	24785
8004009147	6.00	5	12/8/2016	30.00	CleanTool001	RunningMen	12345

Now, I need to calculate as the rules below.

[Average Price in last year] = [total Amount in last year] / [total Quantity in last year]

[Saving Amount in this year] = [Price in this year] * [Quantity in this year] - [Average Price in last year] * [Quantity in this year]

And then, I will have make a virtualization for this [Saving Amount in this year] to drill down by Compay / Plant / Department / ... so on.

So, is there anyone who knows how to make it? It really takes a lot of time for me to study.

Thank you in advance.

Larry

Re: How to calculate average price in previous year?

sunny_talwar — Sat, 27 Aug 2016 14:19:13 GMT

Read here about The As-Of Table‌ and may be try to implement it in your scenario.

Anonymous — Fri, 23 Dec 2016 02:06:08 GMT

Thanks Sunny. I will read it.