2 Replies Latest reply: Dec 22, 2016 9:06 PM by Larry LI RSS

    How to calculate average price in previous year?

    Larry LI

      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 NoPriceQuantityDateAmountMaterial NoCompanyVendor No
      800400914524.001013/8/2016240.00Computer009Fussery34785
      800400914612.001013/8/2016120.00Mouse009DuckPrint24785
      80040091476.00512/8/201630.00CleanTool001RunningMen12345

      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