Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

evgeniystuchalk
Contributor II

Show empty dates on chart

Hello! I have measure with formula, that counts call outs:

Count({$< [Тип события]={'CALL_OUT'}, [Тип разговора]={'Разговор состоялся'}>}DISTINCT [ID события])

But weekends dates not displayng in the chart with 0 values (i'm using master calendar, so this dates exists)

e1d0d3b059.jpg

i know about method to write something like +sum({$<[Year]={"*"}>}0), but after it i can see on chart all dates of calendar, that can go far in to future:

61d53fd4c7.jpg

How to display on chart only dates, that no more than today and no less than 01.01.2015? My current formula is

Count({$< [Тип события]={'CALL_OUT'}, [Тип разговора]={'Разговор состоялся'}>}DISTINCT [ID события])+sum({$<[Year]={"*"}>}0)

4 Replies

Re: Show empty dates on chart

How about this:

Count({$< [Тип события]={'CALL_OUT'}, [Тип разговора]={'Разговор состоялся'}>}DISTINCT [ID события]) +Sum({$<DateField = {"$(='<=' & Date(MakeDate(2015, 1, 1), 'DD.MM.YYYY') & '<=' & Date(Today(), 'DD.MM.YYYY'))"}>} 0)

Re: Show empty dates on chart

I would further ignore selections in any date fields where you might make a selection:

Count({$< [Тип события]={'CALL_OUT'}, [Тип разговора]={'Разговор состоялся'}>}DISTINCT [ID события]) +Sum({$<DateField = {"$(='<=' & Date(MakeDate(2015, 1, 1), 'DD.MM.YYYY') & '<=' & Date(Today(), 'DD.MM.YYYY'))"}, YEAR, MONTH, MONTHYEAR, QUARTER>} 0)

evgeniystuchalk
Contributor II

Re: Show empty dates on chart

Sorry for long answer. I'm used your formula, but still getting iceberg on chart.

E2pgenlUBLPgRm.jpg

uday_pasupuleti
Contributor II

Re: Show empty dates on chart

Try this


Count({$< [Тип события]={'CALL_OUT'}, [Тип разговора]={'Разговор состоялся'}>}DISTINCT [ID события])  


+ 0 * Count({1< [Тип события]={'CALL_OUT'}, [Тип разговора]={'Разговор состоялся'}>}DISTINCT [ID события])  

Community Browser