Forum Discussion
Cumulative Total with Multiple Dates
Hi All, I have an urgent client request that i cannot seem to get to work.
I have a dataset that contains multiple date columns (please see attached a sample). I need to show a a cumulative bar/line chart that shows Actuals vs. Forecast.
Actuals = count of all activities that have been an Activity Status of 'Completed' (grouped by month)
Forecast = count of all activities (grouped by month)
I essentially need all three activities to be shown on one graph like the below, however, because of how the data is structured, i cannot seem to get what i need :(. Is anyone able to shed some light on this. Any help is appreciated. Thanks so much.
The bars should show cumulative actuals (sum of Activity 1 + Activity 2 + Activity 3 where status = Completed) and the line chart should show Forecast Due Date for all three activities.
8 Replies
- Ashish_Mathur
Super User
Hi,
If on the y-axis you want to show % complete, then how can the line show "Forecast Due Date for all three activities"?
Hi Ashish,
Sorry that was my bad for a poor explanation.
Essentially, the count of all activities where forecast due date <= Today should give you the total acivities due as of today. In terms of the line graph, it should show you at any month, what is the expected completed activities. So for Jan 2021, how many activities were forecasted to be completed. The logic for month Jan would be count of all activities where forecast due date <= end of Jan, then feb would be count of all activities (from jan 2021 to end of feb), march would be (count of all activities from jan 2021 to end of march), etc.
I'm struggling becuase every activity has a different forecasted due date and i need all three activities to be summed and shown on the same axis (grouped by month)
Hope that made sense.
- Ashish_Mathur
Hi,
It will be great if you can share a small dataset and show the expected result in a Table format. Don't both about the graph. Once the data in the Table shows the correct result, we can always switch the visual to our desired one.