I can do a quick fix by editing the axis and making it fixed for 12 months in a year, but this creates a problem because this chart is part of a dashboard that can be filtered by year. Is there a way I can create a date value where there is none? For example, if Product B has 5 sales in June and 6 sales in July, but no sales any other month of the year, (there are no values in the data set for the other months i.e. no sales values, nor date values), how can I create a date variable with null or "0" values to fill the series and show up in the graph? One that can ultimately be filtered by year on the dashboard. Some of our products have sales each month, but there are many that do not, and only the dates when the sales are made are recorded.
When I tried clicking on the date to select "show missing values", that option does not appear, because the dates themselves are not associated with product B (I'm assuming).
you can use "BINS" and "LOD", just need to create :
- calculated field named Day : day([sales_date])
- create bin from Day (right click from field Day and click Create and choose Bins) named Day_Bin
- for size of bins, input 1
- if you want to show value total sales per day then create calculated field using LOD (Level of Detail) and choose Fixed Function, named your new field with Total sales per Day
{Fixed Year([Sales_Date]),Month([Sales_Date]), Day_Bin:Sum(ifnull([Sales],0))}
and then in canvas colums you can put : Year([Sales_Date]),Month([Sales_Date]), Day_Bin
and in canvas rows you can put : sum([Total sales per Day])
