Object Count By Limiting Object Entries

[Community_Admin]

Question:

I have a list of items that have multiple date-oriented entries. I have a numerical field that indicates days since last scan, so I can filter based on the lowest number of days per item. What I am trying to do is count the number of items, based on the lowest number of days since scan and categorize them by that number of days (0-30, 30-60, 60-90 for example). I have not been able to accomplish this in an indicator widget. Only in pivot using If statement and a counter.

 

Answer:

The easiest way to do this is to use a Case statement within the elasticube itself. You can do it as either a custom field on your current table, or create a custom SQL Query. For example (see code block), I bucketed days for a Contract End field we have in sales force to help identify outstanding renewals. You can then use the new field in a widget, and count on a unique value to get your counts. For getting the counts, if you don't have an easily identifiable unique or key field, I have also found using the idea of a "Power of One" helpful (see screenshot). Then for your "count" you can sum on the Power of One.

CASE WHEN DayDiff(a.Contract_End_from_LD, Now()) < 0
 THEN ' Past Due'
 WHEN DayDiff(a.Contract_End_from_LD, Now()) >= 0 AND DayDiff(a.Contract_End_from_LD, Now()) < 15
 THEN '0-14 Days'
 WHEN DayDiff(a.Contract_End_from_LD, Now()) >= 15 AND DayDiff(a.Contract_End_from_LD, Now()) < 31
 THEN '15-30 Days'
 WHEN DayDiff(a.Contract_End_from_LD, Now()) >= 31 AND DayDiff(a.Contract_End_from_LD, Now()) < 61
 THEN '31-60 Days'
 WHEN DayDiff(a.Contract_End_from_LD, Now()) >= 61 AND DayDiff(a.Contract_End_from_LD, Now()) < 91
 THEN '60-90 Days'
 WHEN DayDiff(a.Contract_End_from_LD, Now()) >= 91
 THEN '90+ Days'
 END