Object count by limiting object entries

Comments

3 comments

  • Avatar
    Megan Moss

    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


    0
    Comment actions Permalink
  • Avatar
    Peter Sabbag

    Thanks Megan, I may be able to use this. Anybody able to provide a solution as a formula?

    0
    Comment actions Permalink
  • Avatar
    David Hertzberg

    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.

    0
    Comment actions Permalink

Please sign in to leave a comment.