topic Re: Missing values on time dimension bar-chart in QlikView

Missing values on time dimension bar-chart

varmekontrol — Sat, 16 Nov 2024 04:00:16 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(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(if(created>=today(), sales)) //But how to get the null values?

//like this? 
sum(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

jyothish8807 — Mon, 01 Apr 2019 06:28:03 GMT

Hi Varme,

May be like this:

Re: Missing values on time dimension bar-chart

varmekontrol — Mon, 01 Apr 2019 11:34:40 GMT

@jyothish8807

Thank you for your reply. I am using a personal edition and have no keys left to open other .qvw files. Could you maybe post the script of a screenshot for your solution?

Thanks in advance.

Re: Missing values on time dimension bar-chart

jyothish8807 — Mon, 01 Apr 2019 11:52:25 GMT

Try this:

Script:

Inline:
LOAD * INLINE [
created
00:00
01:00
02:00
03:00
04:00
05:00
06:00
07:00
08:00
09:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00
];

Data:
LOAD * INLINE [
created, sale
08:00, 2
11:00, 3
15:00, 7
];

On UI: Create a Bar chart:

Dimension: Created

Expression: sum(sale)

From presentation tab, uncheck suppress zero value