Range of Dates in a period

[#1]

Hi all,

I have two dates:

Start Date: 2012-05-22

End Date:  2012-12-31

I would like to know if there is any function that brings me all dates betwen Start an end date, like that:

2012-05-22
2012-05-23
2012-05-24
2012-05-25
2012-05-26
2012-05-27
2012-05-28
2012-05-29
2012-05-30
2012-05-31

and so on...

[swuehl]

What do you want to do with these dates? Where do you want to show them?

You can easily create a field containing the date range using

Set DateFormat = 'YYYY-MM-DD';

DateRange:

LOAD

Date('2012-05-22'+recno()-1) as Date

autogenerate '2012-12-31'-'2012-05-22'+1;

[#3]

I have a Product_ID and I have the Price and starr and end date of this specifc product.

now i have to create a bar chart tho show the sum(price) of an amount of Product_ID that Its suration is in betwen start and end date.

[swuehl · Accepted solution]

Sorry, I haven't understood the last sentence.

You have a table like

Product_ID     Price     StartDate     EndDate

001               100        2012-01-01     2012-01-31

001               110         2012-02-01     2012-02-29

...

So this table tracks price changes?

And you want to display what? The price changes over time? You could create a master calendar with all dates that you need (e.g. using the code snippet above), that do an interval match load (please check the INTERVALMATCH load prefix in the Help).

Intervalmatch (Date, Product_ID) LOAD StartDate, EndDate, Product_ID resident ProductTable;

[#5]

Sorry, It is not suration It is DURATION.

basicaly I want to select an YEAR and the chart should display the sum(price) of all documents that its date is a valid due date.

[swuehl]

I still haven't fully understood your requirements, sorry. But I assume that using an Intervalmatch between your fact table with Product_ID and SalesDate and the slowly changing dimension (price table) should work.

Could you post a few sample lines of your tables (best as INLINE table to copy & paste in QV) or even better a small QV file?

[#7]

I think it s not necessay, i solve it with Intervalmatch (Date, Product_ID) LOAD StartDate, EndDate, Product_ID resident ProductTable;

thanks a lot again.

you rock!!!

[demoustier]

Good day,

I know it's a old story but a take my chance anyway:

Do you have any solution when Startdate and Enddate are within the same day ?!

In this case, Can't we "force" the interval match ?

[demoustier]

Good day,

I know it's a old story but a take my chance anyway:

Do you have any solution when Startdate and Enddate are within the same day ?!

In this case, Can't we "force" the interval match ?