1 Reply Latest reply: Jul 16, 2013 6:13 AM by Gysbert Wassenaar RSS

    Table comparing current and previous dates using Set Analysis

    Eduardo Gonzalez Servant

      Hi everyone,

       

      I have an issue which I am trying to solve but no luck.

      This issue is relative to a 'Simple Table', just one dimension (date) and two Expressions:

      1) sum of values for current date

      2) sum of values for prior date

       

      The issue I am facing is relative to the way to set the Expression in case 2 using 'Set Analysis'.

       

      Example:

       

      Values:

      Date                |  Value

      10/01/2012      | 29

      11/01/2012      | 30

       

      Here is an example about what I would expect to see.

       

      Result expected (Simple Table):

      Dates                    Sum (Current date)               Sum (Prior Date)

      11/01/2012               30                                             29

      10/01/2012               29                                                -

       

       

      Thanks a lot

        • Re: Table comparing current and previous dates using Set Analysis
          Gysbert Wassenaar

          That can't be done with set analysis if you use date as dimension. The set is calculated once for the entire chart, not per row. What you can try is using the above() function: above(sum(value)). Or you can create a field in the script. If the data in the excel file is already sorted by date then you can use this:

           

          Datos:

          LOAD date as date1,

               value,

               previous(value) as prev_value

          FROM

          data.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

          Other wise you'll need a resident load

           

          Temp:

          LOAD date as date1,  value

          FROM data.xlsx (ooxml, embedded labels, table is Sheet1);

           

          Datos:

          LOAD date as date1,

               value,

              previous(value) as prev_value

          resident Temp

          order by date;

           

          drop table Temp;