topic Table comparing current and previous dates using Set Analysis in QlikView

Table comparing current and previous dates using Set Analysis

Tue, 16 Jul 2013 09:42:02 GMT

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 — Tue, 16 Jul 2013 10:13:32 GMT

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;