Chart dimension skipping some values for the measure

[etiennesan]

Hello everyone,

Here is what I am trying to do: I have for each product ID the monthly sales forecast in one table, and then in another one for each ID and for each site I have one line per daily orders. Also, I have another table with the dates where I can ship (removing weekends for example).

I am trying to have a chart where I would be able to see for the current month (for the dates I have loaded) how am I progressing compared to the forecast, with the forecast just increasing every day by the monthly value divided by the number of days, and then seeing a cumulating line with the orders up to that day.

I have tried naming my calendar field in the same name as the order days to show them on the same graph, and using the below two formulas to get my two lines:

rangesum(above(sum(Forecast)/vNumberOfDays, 0, rowno()))
rangesum(above(sum(order_volume), 0, rowno()))

My problem with this is the following: the forecast is only calculated for the days where there are shipments, otherwise it is not calculated, resulting in a false value at the end (some days are skipped in the graph and the line is not increasing), and the graph ends at the latest date where there is a shipment, not the last one of the calendar.

On the picture below, the end value should be around 1.4k, and as we can see a new forecast value is only calculated where there is an order one.

Do you have any idea on how to do this? Is it possible to display the order line by comparing the order date with the shippable date in the dimension?

Thank you for your help!

[etiennesan · Accepted solution]

Could solve it by using a master calendar, and then by changing my forecast table from monthly base to daily (putting zero in the days of the months that are not in my shippable calendar).

Also, by changing the formula in my graph, I have a line that is stopping at the current date:

If(shippingDate>Date(Today(1)), null(), rangesum(above(sum(order_volume), 0, rowno())))

And then, I have the result I wanted:

Hope it can help someone else!