Solved: Re: Date as Dimension - Qlik Community

felix_kraemer · ‎2018-07-18

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

felix_kraemer · ‎2018-07-19

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

YoussefBelloum · ‎2018-07-18

Hi,

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

felix_kraemer · ‎2018-07-18

Hi Youssef,

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

YoussefBelloum · ‎2018-07-18

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

felix_kraemer · ‎2018-07-18

Same thing

YoussefBelloum · ‎2018-07-18

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

MarioCenteno · ‎2018-07-18

Try

The function Day(field_date) in the dimension.

Mario Centeno

felix_kraemer · ‎2018-07-19

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 · ‎2018-07-19

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

felix_kraemer · ‎2018-07-19

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.