topic Data problem - Show aggregate level data at detail level in QlikView

Data problem - Show aggregate level data at detail level

Tue, 15 Nov 2011 16:56:10 GMT

Hi,

The data I have is as follows:

Product, PeriodStartDate, PeriodEndDate, SalesValue

A, 26/09/2011, 03/10/2011, 800.00

I want to display data like this:

Product, PeriodDate, SalesValue

A, 26/09/2011, 100.00

A, 27/09/2011, 100.00

A, 28/09/2011, 100.00

A, 29/09/2011, 100.00

A, 30/09/2011, 100.00

A, 01/10/2011, 100.00

A, 02/10/2011, 100.00

A, 03/10/2011, 100.00

(The Sales Value is divided by the No. of days in the period i.e. 800/8 = 100).

Do I need to creat an intermediate table?

Regards

Raj

Re: Data problem

Tue, 15 Nov 2011 16:58:10 GMT

SalesValue/(PeriodEndDate-PeriodStartDate) as NewSalesValue

This should work

Data problem

Tue, 15 Nov 2011 17:02:00 GMT

Hi marcsliving

,I dont think I explained well.

I want the aggregated information that I have to be displayed in a detail manner. Could you tell me how to generate the dates between the start and end dates

Data problem - Show aggregate level data at detail level

swuehl — Tue, 15 Nov 2011 17:40:26 GMT

Maybe like this:

INPUT:

LOAD * INLINE [

Product, PeriodStartDate, PeriodEndDate, SalesValue

A, 26/09/2011, 03/10/2011, 800.00

];

Result:

LOAD Product,

Date(PeriodStartDate+iterno()-1) as Date,

(SalesValue / (PeriodEndDate-PeriodStartDate+1) ) as Value

resident INPUT while PeriodStartDate+IterNo()-1 <= PeriodEndDate;

Hope this helps,

Stefan

Data problem

Tue, 15 Nov 2011 17:49:02 GMT

Hi Stefan,

that's absolutely amazing. I was trying with the for loop. But this is much easier with the iterno() and while loop.

Thank You!