I have an interesting use case I am trying to solve. Imagine a table named "Activity" with a record per day for each day of the year. The record has 4 values
1. "Starts" with value 1 if on that day a project was started and 0 if not
2. "InProgress" with value 1 if on that day the project was in progress and 0 if not
3. "Ends" with value 1 if on that day a project was completed and 0 if not
4. A unique ProjectCode which is linked to a Project table with each unique project represented once
I want to create a dashboard with a date filter say 1/1/2018 through 5/31/2018 and I want to add 3 widgets
1. The number of projects started in the date range
2. The number of unique projects in progress on the last day of the date range
3. The number of projects ended in the date range
As an additional requirement I want to be able to click on the widget using the the jump to dashboard plugin and actually see those projects, i.e. the started ones, the ones in progress, and the ones completed.
If you don't have the click through requirement its easy,
1. Set the filter to 5/31/2018
The problem with this is that you can't click through on The Starts and Ends widgets to see all unique projects that where started in the Range 1/1/2018 through 5/31/2018 because the filter is just one day and will get carried through and thus it will only show projects started or ended on 5/31/2018
So I thought of a different way
1. Set the filter to 1/1/2018 through 5/31/2018
2. CountUnique(ProjectID) with filter Starts = 1
3. CountUnique(ProjectID) with filter InProgress=1
4. CountUnqiue(ProjectID) with filter Ends=1
The problem with this is that although you can click through the InProgress count represents all projects that at some point where in Progress during the time range and not just on the last day of the month
So both ways have a draw back and we may have to use two date filters, one for 1/1/2018 through 5/31/2018 and one for just 5/31/2018. But this is super confusing for the user. They want to set 1 filter, either 1/1/2018 through 5/31/2018 or even better just 5/31/2018
So how do I solve this?
A couple of thoughts I had that probably are not supported
A) If I could somehow in a formula could say (SUM(InProgess), LASTDAYOFRANGE(DATEFILTER)) which would turn the range 1/1/2018 - 5/31/2018 into 5/31/2018 the count of in progress would be right but the click through would likely still show all projects that at some point where in progress
B) If on a JumpToDashboard it would realize a YTD(DateFilter) was used and instead of passing through just the DateFilter, i.e. 5/31/2018 it would grab the whole year through 5/31/2018 and put it into the filter it would work
C) If in the script of the widget I could somehow let it know to pass the filter "FirstDayOfYear(DateFilter) through DateFilter" as a filter it would work. I.e. I let the widget now what to pass through in the Date Filter on a jump to
I figure C) has the best chance of actually being possible but I don't even know where to start on that
Anyone have any ideas?
Please sign in to leave a comment.