Calculated Dimensions

[kingsofdeleon]

The idea here is to expand the formula editor (Specifically CASE WHEN Statements) to allow the creation of dimensions or "buckets" based on filtered data. This is useful on the dashboard level as we may want to recategorize our data dynamically after filtering the dashboard.   

Lets say I have a pretend table holding daily customer transactions. In my report I'd want to see each customer's average spending for an arbitrary time frame. I'd also like to categorize that player BASED off that average spending. Which could change based on any filters I use. 

IE)

CASE

WHEN [Avg Money Spent] > 999 then '1000+' 

WHEN [Avg Money Spent] > 699 then '700 to 999'

WHEN [Avg Money Spent] > 399 then '400 to 699'
...
WHEN [Avg Money Spent] >= 0 then '0 to 99'
ELSE NULL 
END

I would then use this calculated dimension in visualizations like pie charts, etc.

I'm not sure if this is possible, but being able to use this dimension as a filter would be very useful as well.

IE) Show me all the customer who fall under '400 to 699' range for any given period.

[wallingfordce]

Workarounds for this:

For the pie chart, don't put anything on categories and instead create a value per bucket.

e.g. IF ( [Avg Money Spent] > 999 , 1 , 0 ) and rename the value to "1000+" etc.

And for a filter, say a Pivot with customer details on rows and values for spend, put a widget filter on the customer ID, Ranking type, TOP 100000 with a formula like IF ( [Avg Money Spent] > 999 , 1 , NULL ), where NULL will exclude non-qualified customers from being included in the ranking at all, and thus excluded from the scope of the widget.

[wallingfordce]

I realized after posting that I forgot to put that first formula into a MPA, so it'd need to be more like:

SUM ( [Customer ID] , IF ( [Avg Money Spent] > 999 , 1 , 0 ) )

[irismaessen]

 I wonder if the 'Dynamic Buckets' plugin doesn't do most of what you want. The definition is in the widget script, not the formula, but you should be able to get results.

Iris

[kingsofdeleon]

Thank you very much for the input everyone.

I'll report back to let you know if these suggestions worked!