Splitting time taken to resolve data by 30/60/90 days
Looking to provide metrics on counting time taken to complete assignments split by 30/60/90 days.
Working in the pivot table grouping by "Department" I want to display 3 columns based on DDIFF (start_date and end_date) so that I can see count of those records where time was 0-30 days, 30-60, 90+.
As a test below I have attempted to get just <10 and >10 - this is clearly not equal to the count of all so something is not working.
Column formula (and I have also tried with SUM with essentially the same result, DUPCOUNT gives an error as I can't aggregate)
COUNT([ID],IF(MAX(DDiff([Days in START_DATE],[Days in END_DATE]))<10,1,0))
I only have access to dashboard designer permissions so can't edit any elasticube settings
Hi AlexW,
SUM should be used to get the total of the 1s and 0s, i.e. the count of records that match each bucketing criterion. Here's how the formula should look like:
SUM([ID], IF(MAX(DDiff([Days in START_DATE],[Days in END_DATE]))<10,1,0))
I also attached a sample dashboard below for your reference. This dashboard uses the Sample Healthcare cube, which is available by default to all dashboard designers. Note: please change the file extension from .txt to .dash before importing to your environment.
