topic Missing values on time dimension bar-chart in QlikView

Missing values on time dimension bar-chart

varmekontrol — Sat, 16 Nov 2024 04:00:15 GMT

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 🙂 )

created	sales
08:00	2
11:00	3
15:00	7

what i want

created	sales
00:00
01:00
02:00
03:00
04:00
05:00
06:00
07:00
08:00	2
09:00
10:00
11:00	3
12:00
13:00
14:00
15:00	7
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00

Re: Missing values on time dimension bar-chart

haupenthals — Sun, 31 Mar 2019 12:49:10 GMT

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

Re: Missing values on time dimension bar-chart

varmekontrol — Sun, 31 Mar 2019 12:53:27 GMT

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

Re: Missing values on time dimension bar-chart

haupenthals — Sun, 31 Mar 2019 13:31:44 GMT

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?

Re: Missing values on time dimension bar-chart

pradosh_thakur — Mon, 01 Apr 2019 05:31:33 GMT

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)))

Re: Missing values on time dimension bar-chart

pradosh_thakur — Mon, 01 Apr 2019 05:33:23 GMT

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

Similar to

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

Re: Missing values on time dimension bar-chart

SunilChauhan — Mon, 01 Apr 2019 06:43:06 GMT

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 .