topic Re: Best practice for bucketing/banding data in Dashboards & Analytics Discussions

Best practice for bucketing/banding data

RADSr — Thu, 19 Jan 2023 17:57:08 GMT

I'd like to do an aggregated list e.g. Client, Net Sales, No of Purchases, No of Returns and would then like to group this list by a user-defined bucket e.g.

CASE(AND(Net Sales > 1000000), No of Returns < 10), 'Great', 'not great' )

But of course the above won't work because it will evaluate every source row and everything will be not great.

What I really want is: CASE(AND(sum(Net Sales > 1000000)), sum(No of Returns) < 10), 'Great', 'not great' )

but that nets me this error message:

sum currently not supported in this context. It is currently supported only in insight formulas.

Leaving aside the fact that I **AM** using this in an insight formula ( so thanks for that error-message-author ) can I accomplish this in an insight or do I need to create more MVs?

Re: Best practice for bucketing/banding data

RADSr — Fri, 20 Jan 2023 05:26:06 GMT

The solution I put in place for the moment is using an insight based on a result set. I'm not terribly pleased because 1) it's not simple for casual authors/explorers to build, 2) it's a bit buggy,* and 3) when I have more than one insight they are no longer connected via the same fields ( i.e. when I click a bucket in one insight the other is not automatically filtered ).

* - The moderate buggy bit is that when I click a value in the insight to filter it does, but when I click the filter to change the value there are no other values displayed. I have to delete the filter "pill" and re-select.

The very bad buggy part is that I've been getting errors where the insight seems to lose the reference to the result set and completely breaks. Unfortunately it has happened several times but is not purposely replicable ( at least so far ).

Yes I should log a ticket. No I haven't yet. 😉

Re: Best practice for bucketing/banding data

JoeM — Fri, 20 Jan 2023 16:59:30 GMT

@RADSr - we'll give one more thing a try to see if this solves it for you. This is a formula that you might be able to leverage but note it would have to be applied to an aggregated table.

IF( and( sum( Community_Data.Client_Banding.Net_Sales, groupBy( Community_Data.Client_Banding.Client ) ) > 10000, sum( Community_Data.Client_Banding.No_of_Returns, groupBy( Community_Data.Client_Banding.Client ) ) < 10 ), 'Great', 'Not Great' )