Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data problem - Show aggregate level data at detail level

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

4 Replies
Not applicable
Author

SalesValue/(PeriodEndDate-PeriodStartDate) as NewSalesValue

This should work

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

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!