Convert custom table data to vertical stacked bar chart

Question & Answer
,
1

I’m using the following calculated field formula to determine if each customer in a given month is a repeat customer (has purchased in the past 3 months), a reactivated customer (hasn’t purchased in the past 3 months, but has prior to that) or a new customer.

ifelse(isNull(lastPurchaseMonth),'New', ifelse(lastPurchaseMonth >= addDateTime(-2, "MM", truncDate("MM", max({updated_at}))), 'Repeat', 'Reactivation'))

The calculated field seems to be working well and I can generate a table that looks like the following:

Column 1 Column 2 Column 3 Column 4
Dec 2023 Customer 1 Repeat
Dec 2023 Customer 2 Repeat
Dec 2023 Customer 4 New
Nov 2023 Customer 1 New
Nov 2023 Customer 2 Reactivation
Nov 2023 Customer 3 Repeat

I want to put this in a vertical stacked bar chart so that I can easily see how many total unique customers we had each month and the breakdown between repeat, reactivations, and new customers. When I put the month as the x axis, customer as the value, and try to drop my calculated field in the group/color, it says “Custom aggregation field is not allowed as a dimension”. Is there any way around this to create the visualization I’m trying to create?

2

@Josh_H it will be better if you transpose your table before storing it into spice.

1 Like
3

Got it, so the recommended way to solve these types of errors is to make the calculations outside of Quicksight and then use the calculated data as the dataset? Is there a workaround in Quicksight or is that the only way?

4

@Josh_H diff problem diff approach.
Not a rock solid line.