Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Have you tried to uncheck/check Suppress Zero-Values and Suppress Missing in the Presentation tab of the chart's properties?
Yes, I have tried every thinking combination of those settings.
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?
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)))
Or may be multiply the expression you have by avg({1<created=>}1)
Similar to
your expression * avg({1<created=>}1)
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 .