Table comparing current and previous dates using Set Analysis

[#1]

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

[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;