Date as Dimension

[felix_kraemer]

Hello,

I have the following challenge.

My data source shows sales of cars and for each sale I have a date stamp.

I want to create a graph with Dimension date and KPI sales of car. On days where no car has been sold it should Show the date with Zero sale.

Right now it only shows Dates with sales.

Can someone please help me?

Thank you.

Felix

[YoussefBelloum]

you don't have those dates in your data model.. you should generate them using a calendar table or inside your table

[felix_kraemer]

Sounds nice, can you tell me how I should do that?

[YoussefBelloum]

the main idea is to pick the Minimum date value and the maximum date value and generate all the dates values between those two dates, load that values in a dimension table called "calendar" or whatever you want and create derived date fields from the field Date (like year, month, quarter etc etc). like this you will have a Calendar-dimension-table linked to your fact table and you will be able to analyze your measures using time axis

I invite you to take a look at the different techniques you can easily find on the community to create this table and let me know if you're stuck on something

[felix_kraemer]

Many thanks Youssef

[YoussefBelloum]

you're welcome good luck

[d_prashanthredd]

Hi Felix,

Only the reason should be no sales on so and so dates. It include missing dates, you need to create a master calendar.

You can ignore other day/date related fields from the below script.

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

     min(SalesDate) as minDate,  //here SalesDate is from your table which referes to actual date

     //(1) as maxDate  //you can use this to fetch all the dates till now.

     max(SalesDate) as maxDate //if you want to get all dates till the last sales date

Resident RegionsData;  //You should resident from your data source in which table you have that date field

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

     $(varMinDate) + Iterno()-1 As Num, 

     Date($(varMinDate) + IterNo() - 1) as TempDate 

     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

     TempDate AS [Date Entered], 

     week(TempDate) As Week, 

     Year(TempDate) As Year, 

     Month(TempDate) As Month, 

     Day(TempDate) As Day, 

     YeartoDate(TempDate)*-1 as CurYTDFlag, 

     YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

     WeekDay(TempDate) as WeekDay 

Resident TempCalendar  Order By TempDate ASC; 

Drop Table TempCalendar; 

Thanks,

Prashanth Reddy D.

[d_prashanthredd]

in above script @ TempDate AS [Date Entered]

Instead [Date Entered] you link it to your actual date field to join.

[felix_kraemer · Accepted solution]

Hi Prashanth,

thank you for posting the script.

I changed the fields you mentioned but get an error while loading. "Field 'a' not found"

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

     min(START_DATE_WITHOUT_SET_TIMES) as minDate,  //here SalesDate is from your table which referes to actual date

     //(1) as maxDate  //you can use this to fetch all the dates till now.

     max(START_DATE_WITHOUT_SET_TIMES) as maxDate //if you want to get all dates till the last sales date

Resident Tabelle1;  //You should resident from your data source in which table you have that date field

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp; 

TempCalendar: 

LOAD 

     $(varMinDate) + Iterno()-1 As Num, 

     Date($(varMinDate) + IterNo() - 1) as TempDate 

     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

     TempDate AS [START_DATE_WITHOUT_SET_TIMES], 

     week(TempDate) As Week, 

     Year(TempDate) As Year, 

     Month(TempDate) As Month, 

     Day(TempDate) As Day, 

     YeartoDate(TempDate)*-1 as CurYTDFlag, 

     YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

     WeekDay(TempDate) as WeekDay 

Resident TempCalendar  Order By TempDate ASC; 

Drop Table TempCalendar

[felix_kraemer]

Found my mistake.

Many thanks for your help