4 Replies Latest reply: Feb 10, 2016 8:53 PM by Dale Freya RSS

    Aggregate over data in same table except self (Data load script, Qlik Sense)

    Ryan Carrier

      I have data that I want to also display data from the years previous, we are using Years/Weeks rather than Years/Months/Days so I'll use that in my example.

       

      Currently I have a table similar to this;

      YearWeekAB
      201611015
      20162510
      201511218
      20152618
      20141312

       

      Now I want it to just be this year, but to add another field based on the previous years with the same week.
      So for example;

      C= B/A

      Week 1, (18+12)/(3+12)=2

      Week 2, 18/6=3

      YearWeekABC
      2016110152
      201625103

       

      Obviously I need it for all 52 weeks of the year and there are also many more columns, but they should be irrelevant regarding this example.

       

      I have thought to make  for each 1 to 52 load and save it into 52 temporary qvds and join them all up.
      If it was possible to nested load, so load with condition of the weeks being equal but year beeing less than, then aggregating that, it would work but it doesn't look like that is possible.

      I could create another temp table with all 2016 years taken out, then aggregate based on week, then join back to the original table, this is the one I will probably work on until/if a easier response is stated. I'm sure it's possible in an easy way I just can't seem to see/find it.

       

      Thanks for your time.

       

      Ryan