8 Replies Latest reply: Feb 2, 2018 4:00 AM by Jose Miguel Coelho RSS

    Calculate MoM variation

    Jose Miguel Coelho

      Hello,

      I'm fairly new at using Qlik Sense, albeit on the past weeks I've been using quite a lot to change my tools from excel. In all the cases I've been able to solve my problems with the help of this community, except for the one I'm posting today.

       

      This is a simplified sample of the data, in the real case I have more than a million lines and many more columns:

        

      PNFamilyCountryUploadCost
      CPU1CPUATJan-18100
      CPU2CPUATJan-18120
      Memory 32 GBMemoryATJan-18140
      CPU1CPUDKJan-181000
      CPU2CPUDKJan-181200
      Memory 32 GBMemoryDKJan-181400
      CPU1CPUATFeb-1890
      CPU2CPUATFeb-18110
      Memory 32 GBMemoryATFeb-18130
      CPU1CPUDKFeb-18900
      CPU2CPUDKFeb-181100
      Memory 32 GBMemoryDKFeb-181300
      CPU1CPUATMar-1880
      CPU2CPUATMar-18110
      Memory 32 GBMemoryATMar-18125
      CPU1CPUDKMar-18850
      CPU2CPUDKMar-181215
      Memory 32 GBMemoryDKMar-181315

       

      PN- Part Numbers, how the product is identified

      Family- Group of Part Numbers make a family. E.g If we have 3 different CPUs (Intel, AMD, QUALCOMM) they still belong to the same family, CPU.

      Country- The country that has the cost using their local currency. AT, Austria (Eur) and DK, Denmark (DKK).

      Upload- The month and year I uploaded the cost from SQL.

      Cost- Cost of that product

       

      What I'm trying to do is to create a chart based on the families month to month (MoM) variation, basically compare the cost of the PN in one month vs the previous month.

       

      The chart should consider the MoM variance per family, but to do that needs to take into consideration the MoM average variance between all different Part Numbers from the same family and also the country. Consider all this is what is creating huge issues for me.

       

      In a mathematical way would be:

      MoM(Feb 2018)= (("Cost for AT Feb-18 for CPU1"/"Cost for AT Jan-18 for CPU1"-1)+("Cost for AT Feb-18 for CPU2"/"Cost for AT Jan-18 for CPU2"-1))/2

      Mar(2018)= (("Cost for AT Mar-18 for CPU1"/"Cost Feb-18 for CPU1"-1)+("Cost for AT Mar-18 for CPU2"/"Cost for AT Feb-18 for CPU2"-1))/2

      and keep doing that for each new month, country and with a much bigger range of Part Numbers.

       

      I'm trying to add in a line chart or combo chart. Normally, I start by considering Upload and Family as dimension and cost as measure.

      I already tried to use aggregates, average by distinct, aboves, sums, counts, but the formulas I've tried till now are not working or giving me the wrong results, whether I'm not considering the countries, or the above formula can't be applied on the way my sample is organized etc. .

       

      Do you know how can I solve this case?

       

      Probably I'm missing something very simple, but can't see what.

       

      Thanks for your help