I'm trying to create a card that shows the trend of case completion against a backlog each month (based on open/close dates). Ideally, it would be a multi-line chart with 'month-yr' on the X-axis and COUNT of cases on the Y-axis, for the following series:
Is this possible? Not really sure how to tackle the aggregation of the backlog each month with beast mode.
I’d do some additional ETL before doing this visualization. There are two issues that would make date-based beast gymnastics in the card a pain:
Here’s a review of the ETL approach I’d take to take to tackle these two issues.
You could now use “Key Date” as your x-axis, Sum of Key Count as your y-axis, and “Row Status” as your series. Make sure this is treating you right, then proceed to the next piece which adds the historical snapshot.
There are other ways to do this using Group By to assemble counts for various conditions, but it wouldn’t allow a user to drill to any details.
If you need to recreate the past EOM Open Cases counts and aren’t worried about drills, you could add a simple webform to the ETL & append with each row representing an EOM period. You’d need to make sure the column types matched so the append worked properly, but it would just be a Row Status of “EOM Open Cases”, a Key Count of whatever value you had logged as open EOM case count, and a Key Date of the last day of each month.