Simple data range question

[pndavkota]

Hi guys,

i'm stucking ni this simple situation where i need ta have bars from a date range (2014-now) but not all date have data in them, how do i generate a local autogenareted table with a date range?

in Oracle i would do someting like this:

SELECT

(to_date(sysdate,'DD-MM-YYYY') - level + 1) AS day

FROM

dual

CONNECT BY LEVEL <= (to_date(sysdate,'DD-MM-YYYY') - to_date('01-01-2014','DD-MM-YYYY') + 1);

[Digvijay_Singh]

I guess you are looking for the master calendar script, you can find lots of responses if you just search for master calendar, I normally use this below one, you would need to update it as per your usage - 

Load distinct
TempDate as _month_date_cal,
Year(TempDate) as [Year],
Month(TempDate) as [Month],
'Q' & ceil(month(TempDate) / 3) as [Quarter],
'Q' & ceil(month(TempDate) / 3) & ' ' & Year(TempDate) as [Quarter Year],
Date(Floor(MonthStart([TempDate],0)),'MMM-YYYY') as [Month Year]
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) as TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Month Year', recno()))-1 as mindate,
max(FieldValue('Month Year', recno())) as maxdate
AUTOGENERATE FieldValueCount('Month Year');