topic Re: Date as Dimension in App Development

Date as Dimension

felix_kraemer — Wed, 18 Jul 2018 15:21:39 GMT

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

Re: Date as Dimension

YoussefBelloum — Wed, 18 Jul 2018 15:49:03 GMT

Hi,

take a look at: Add-ons -> Data handling and check Include zero values

Re: Date as Dimension

felix_kraemer — Wed, 18 Jul 2018 15:59:47 GMT

Hi Youssef,

include Zero values is activated but it still doesn't show dates where is no sales.

Re: Date as Dimension

YoussefBelloum — Wed, 18 Jul 2018 16:05:31 GMT

and how about checking the option "show null values" of the dimension ?

Re: Date as Dimension

felix_kraemer — Wed, 18 Jul 2018 16:19:45 GMT

Same thing

Re: Date as Dimension

YoussefBelloum — Wed, 18 Jul 2018 18:08:46 GMT

‌would you be able to share Sample data or sample app ?

Re: Date as Dimension

MarioCenteno — Wed, 18 Jul 2018 19:04:42 GMT

Try

The function Day(field_date) in the dimension.

Mario Centeno

Re: Date as Dimension

felix_kraemer — Thu, 19 Jul 2018 07:09:47 GMT

Hi,

I uploaded an example app.

As you see the graph only shows days with sales. I want the graph to show all days and leave those days w/o sales blank.

Thanks

Re: Date as Dimension

YoussefBelloum — Thu, 19 Jul 2018 08:22:17 GMT

what are the dates on which you think there are no sales ?

Re: Date as Dimension

felix_kraemer — Thu, 19 Jul 2018 08:51:57 GMT

please see Screen shot below.

I would like to see all dates between 19.01.18 and 27.01.18 in the graph.

Those days wouldn't have sales and should be shown as zero.

Re: Date as Dimension

YoussefBelloum — Thu, 19 Jul 2018 09:02:14 GMT

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

Re: Date as Dimension

felix_kraemer — Thu, 19 Jul 2018 09:05:08 GMT

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

Re: Date as Dimension

YoussefBelloum — Thu, 19 Jul 2018 09:18:02 GMT

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

Re: Date as Dimension

felix_kraemer — Thu, 19 Jul 2018 09:19:52 GMT

Many thanks Youssef

Re: Date as Dimension

YoussefBelloum — Thu, 19 Jul 2018 09:23:24 GMT

you're welcome good luck

Re: Date as Dimension

d_prashanthredd — Thu, 19 Jul 2018 09:24:33 GMT

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.

Re: Date as Dimension

d_prashanthredd — Thu, 19 Jul 2018 09:26:28 GMT

in above script @ TempDate AS [Date Entered]

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

Re: Date as Dimension

felix_kraemer — Thu, 19 Jul 2018 09:54:43 GMT

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

Re: Date as Dimension

felix_kraemer — Thu, 19 Jul 2018 10:12:04 GMT

Found my mistake.

Many thanks for your help