This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
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.
