Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
varmekontrol
Contributor

Missing values on time dimension bar-chart

I know this has been discussed in many threads in this community, but I still am unable to figure out the simplest way to solve my problem.  

I have dates = time(created) 
I have sales = count(sales) 

I would like to see on a 24 hour basis time(created) all count(distinct sales) even if there is none. 

//I know there is something like this
if(isnull(created),' ',sales)

//If I need to make a table or bar-chart on a specific period i need to.
sum(distinct if(created>=today(), sales)) //But how to get the null values?

//like this? 
sum(distinct if(isnull(created>=today(), sales)), ' ', sales)) //this does not work. 

What I have  (imagine a barchart 🙂

createdsales
08:002
11:003
15:007

 

what i want 

createdsales
00:00 
01:00 
02:00 
03:00 
04:00 
05:00 
06:00 
07:00 
08:002
09:00 
10:00 
11:003
12:00 
13:00 
14:00 
15:007
16:00 
17:00 
18:00 
19:00 
20:00 
21:00 
22:00 
23:00 
6 Replies
haupenthals
New Contributor III

Re: Missing values on time dimension bar-chart

Have you tried to uncheck/check  Suppress Zero-Values and Suppress Missing in the Presentation tab of the chart's properties?

varmekontrol
Contributor

Re: Missing values on time dimension bar-chart

Yes, I have tried every thinking combination of those settings. 

image.pngimage.png

haupenthals
New Contributor III

Re: Missing values on time dimension bar-chart

You load only sets with Created when there is a Sum ?
What is the format of Created?

Suppose it‘s 0:00:
Can you try to load an inline table with 24 rows and column 1 named Created with values from 0:00 to 23:00 and column 2 with values 1 named Dummy?

pradosh_thakur
Honored Contributor II

Re: Missing values on time dimension bar-chart

Uncheck the supress zero supress null and then maybe try this 

alt(sum(distinct if(created>=today(), sales)) ,0)
if(len(trim(sum(distinct if(created>=today(), sales))))=0,0,sum(distinct if(created>=today(), sales)))

 

Learning never stops.
pradosh_thakur
Honored Contributor II

Re: Missing values on time dimension bar-chart

Or may be multiply the expression you have by  avg({1<created=>}1)

Similar to 

your expression *  avg({1<created=>}1)

Learning never stops.
SunilChauhan
Esteemed Contributor

Re: Missing values on time dimension bar-chart

Create a Master  Calender  using min and Max time, Generate all the Date and link this table with Master Calender.

 

when you link Master Calender you will have all the time .