generate count of ids for missing dates as zeros

[ioannaiogr]

Hi, 

your help here will be golden.

I have a barchart with x axis date field (master calendar field, so i have all dates) and y axis is count(ids).

Without any other filter selections made, all dates are displayed as the field contains all time. In data handling i have "show zeros" enabled. Also I did the trick of +sum({1} 0) or +sum({1} 0)*0 in the measure, doesn't serve me because by selecting time range in date picker it zeros out all other counts on all the other dates and plus you have to drag the mini chart until you find your desirable time range. They don't want that AT ALL.

I realised when i make a filter selection it only shows the dates available, which is correct, as on the dates not shown , i have no data, so these are missing values. I would like to show bars with counts of 0s when there are missing dates, so generate count of ids as zeros for missing  dates

I don't know how to handle this though because i am the beginner that i am  . 

Any helpful tips please?

Thanks in advance

[rubenmarin]

Hi, maybe with a litle change yo your "sum({1} 0) ", to something like:

sum({1<DateField={">=$(=Min(DateField))<=$(=Max(DateField))"}>} 0) 

Trying to show only zeros on your date range selected instead of all data.

[ioannaiogr]

Hi @rubenmarin , this is actually very helpful but i'm noticing this : Say i'm picking from date range 1/8/2021 to 13/9/2021. Also i'm making a selection in another field in filter pane. This selection has dates available from 1/8/2021 to 1/9/2021, so correctly it shows some 0s in between as it should, but then after 1/9/2021 it doesn't show 0s for the dates 2/9/2021 to 13/9/2021. Also the selection in date range picker changes on its on to 1/8/2021 to 1/9/2021.

Any ideas?

[rubenmarin · Accepted solution]

Hi, thats because selections affects the max an min dates, options are:

- Use an island table to select dates, so they are no affected by any selection

- Use variables for the user to select max and min dates, variable values don't change with selections.

- Add fields to ignore to the expression: sum({1<DateField={">=$(=Min({<ListOfFieldsToIgnore>}DateField))<=$(=Max({<ListOfFieldsToIgnore>}DateField))"}>} 0) 

[ioannaiogr]

I appreciate your really quick help @rubenmarin  🙂

But, the thing is i want and need the chart to get affected by other filter selections. I just want to show zeros for up to the selected by the user date , not just the available for the selection and the dates in between even if data don't exist there! Do the above suggestions solve this? I'm quite new so bear with me 😊

[rubenmarin]

Hi, yes, that's different approaches to make the dates stay as the user selected, so other filters don't change the dates shown on chart.

Each one has his advantages and disavantages. If you have few fields where the user can selct go for the 3rd. If not you have to choose 1st or 2nd, both will need to use set analysis to fiellter dates in each expression.

[ioannaiogr]

I am confused. The field I use the most and need and so i can't ignore in set analysis, is the one that makes the date change on date picker according to max available date for this field value, even though the user had made a broader selection.

I tried with  +sum({1<DateField={">=$(=Min(DateField))<=$(=Today())"}>} 0) 

but, if i for example pick 01/07/2021 to 01/08/2021, it also zeros out every other date beyond this range, which is. well, easy to be misleading to the user! 

@rubenmarin  🙂 

[rubenmarin]

Hi, can you make a dumb sample simulating your data model to work with it?