Handling dates - Assigning statuses as dimension - Methodology

[#1]

Hello QV Experts,

I would like to know the methodology that should be used for what I want to do.

My dataset is looking something similar to this, with thousands of alerts:

Controllers are assigned some alerts that they need to resolve (to close) as fast as they can. Alert is still open when 'date alert completed' is empty.

In my dashboard I want to show over time, what was the status of those alerts at the end of each month:

And would also like to count the number of days each alert stayed open over time and from this create Ageing bucket over time:

I received help to create a master calendar, where I assigned a Status dimension: 'Created', 'Assigned', 'Completed' to it.

I believe I should do something similar but I am not really sure of the methodology...

Would you have any advice on this ? Thank you for your response !

[swuehl]

Maybe have a look at

Creating Reference Dates for Intervals

This should get you in the right direction. The reference date table can be limited to only hold monthly (monthend) data, if needed.

And you can add age classification also to the records in the reference date table.