how to mention dates if there is no data..

[#1]

Hai

I have an doubt please ny one clarify me with that ..?

I have date field in Table T1 and orderamount in a table T2.

I am taking an table chart with date and orderamount..

date is coming for matching orderamount instead of that I want all date wants mention in my chart.If there is no orderamount it should come with null

eg .my table look like this

Date	Orderamount
1-1-09	45
3-5-09	45
9-6-09	26
7-8-10	569

but I want missed dats with null values in between  1-1-09 and 7-8-10 please help..where I want to make changes..?

[luciancotea]

You create a calendar table. Using the min and max date from your table:

MINMAXDATE: // ==== Get First and Last Date in Orders Table ====
Load           Min(Date) as MINDATE, 
                Max(Date) as MAXDATE 
resident ORDERS; // your table name here <-------------------------------------------------------------------
Let           vFirstDate = peek('MINDATE', 0, 'MINMAXDATE') ;
Let           vLastDate = peek('MAXDATE', 0, 'MINMAXDATE') ;
Let           vDateZero = vFirstDate-1; 
Drop Table MINMAXDATE;
Time:  // ============ Create Time Dimension ============
Load          [Date],
          Year([Date]) as Year,
          Week([Date]) as Week,
          Weekday([Date]) as Weekday,
          Month([Date]) as Month,
          Dual(          'Q' & ceil(Month([Date])/3), ceil(Month([Date])/3)) as Quarter,
          Dual(Year([Date]) & '-' & 'Q' & ceil(Month([Date])/3  ), Year([Date])*10 + ceil(Month([Date])/3)) as YearQuarter,
          Dual(Year([Date]) & '-' & week([Date]), Year([Date])*100 + week([Date])) as YearWeek,
          Day([Date]) as DayInMonth,
          Date(MonthStart([Date]),'MMM-YYYY') as YearMonth
          ;
Load          Date($(vDateZero)) + RecNo() as [Date] 
Autogenerate vLastDate - vDateZero; 

[#3]

Thanks for your reply..

I have generated but null value dates are not coming in the chart..

[luciancotea]

You need to go to your chart properties -> Presentation tab and uncheck the "Hide nulls" checkbox