Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
felix_kraemer
Contributor III
Contributor III

Date as Dimension

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

1 Solution

Accepted Solutions
felix_kraemer
Contributor III
Contributor III
Author

 

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

 

View solution in original post

18 Replies
YoussefBelloum
Champion
Champion

Hi,

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

felix_kraemer
Contributor III
Contributor III
Author

Hi Youssef,

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

YoussefBelloum
Champion
Champion

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

felix_kraemer
Contributor III
Contributor III
Author

Same thing

YoussefBelloum
Champion
Champion

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

MarioCenteno
Creator III
Creator III

Try

The function Day(field_date) in the dimension.

Mario Centeno

felix_kraemer
Contributor III
Contributor III
Author

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

YoussefBelloum
Champion
Champion

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

felix_kraemer
Contributor III
Contributor III
Author

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.