• I've always found 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 as 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 `
• Thanks Megan, I may be able to use this. Anybody able to provide a solution as a formula?

• Hi,

Megan's solution is indeed making lots of sense, and what I usually do in such cases.

If your buckets are based on a fixed interval, then you can probably use QUOTIENT, MOD and ROUND function to calculate the buckets with a function. Hope this can help.

Best, David.