Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm looking for display all value of time axis. In my sample in attached file, i have Nb Sales for January 2016. I want display in my chart all value of date, from 01/01 to 31/01. Even for days which no sales has occured.
Last year, i did that in an app with a left join in my load statement and it was ok. What goes wrong here?
Regards
Hi sergio, using a left join only keeps the dates from the first table, you can use 'outer' instead of 'left' to keep dates from both tables.
Or better, generate a calendar: The Master Calendar
You can create a calendar with this script:
ORDERS:
LOAD Date#(Date,'DD/MM/YYYY') as Date, Sales_nb, Seller INLINE [
Date, Sales_nb, Seller
02/01/2016,234,A
09/01/2016,867,B
13/01/2016,908,C
16/01/2016,345,D
19/01/2016,450,E
24/01/2016,345,F
25/01/2016,234,G
29/01/2016,255,H];
LET vMinDate=Num(Date('01/01/2016')); // This can be more dynamic
LET vMaxDate=Num(Date('31/01/2016'));
Calendar:
Load Date,
Month(Date) as Month,
Year(Date) as Year;
Load Date($(vMinDate)+iterno() -1) as Date AutoGenerate 1 While iterno() <= $(vMaxDate) - $(vMinDate);
QlikView joins tables using fields with same name, in the case of a calendar usually is kept in a separate table.
Also, if you want to know more about dates take a look at this posts:
Hi, Ruben. thanks for your reply. I forgot to mention in my post that i have already tried with outer. The result is ok for a table box but not in charts (see my new attached file).
I'll try with creation a master calendar as you propose.
Hi Sergio, in presentation tab-> uncheck 'Supress zero values'
check the Zero on bars, uncheck the Suppress Zero_values