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
1 ACCEPTED SOLUTION
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.
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.
