1 Reply Latest reply: Jul 20, 2017 3:01 AM by Rasmus Nielsen RSS

    Calculating data from resident table

    Rasmus Nielsen

      Hi guys,

       

      I am loading data from an SQL database. I have two columns: date and volume and in my SQL query I limit data to the last 8 weeks. Like this:

       

      MyTable:

      LOAD testdate, volume;

      SELECT

          CAST(timestamp AS DATE) as testdate

          SUM(volume) as volume,

      FROM $(dbname).dbo.TestTable

      WHERE timestamp >= DATEADD(week,-8, GETDATE())

      GROUP BY CAST(timestamp AS DATE)

       

      I now wish to create a new Qlik table where I have the following columns:

      date || volume || volume same day last week || volume (8 week average)

       

      The point is that I only wish to have rows for the last 7 days in my new table, but I need 8 weeks of data to calculate data for all rows in the new table. How would I go about calculating data for the last two columns?

       

      I am new to Qlik and Im not sure about the syntax.

       

      Thanks!

      Best Regards,

      Rasmus