Creating "buckets" on Quicksight - Using custom aggregated field as a dimension

Question & Answer
, , , , , , ,
1

Hi all, I’m faced with the following problem:
I have a table that has the following columns of importance:
orderDate, orderID, userID, userName, orderValue
My goal is to find out the bucket in which a user belongs, i.e., a user placed between 5-10 orders in in the current month, 10-15, 15-20, etc. I have done the following calculations:
numberOfOrders:
count(id)
I then used this calculated field to create another calculated field that defines the buckets.
orderBuckets:

ifelse(
    numberOfOrders < 5, "< 5 orders",
    numberOfOrders >= 5 AND numberOfOrders <= 10, "5-10 orders",
    numberOfOrders > 10 AND numberOfOrders <= 20, "10-20 orders",
    numberOfOrders > 20 AND numberOfOrders <= 30, "20-30 orders",
    numberOfOrders > 30 AND numberOfOrders <= 40, "30-40 orders",
    numberOfOrders > 40 AND numberOfOrders <= 50, "40-50 orders",
    "> 50 orders"
)

However, when I add this calculated field as a dimension, it doesn’t work since this is a custom aggregated field. I was able to get this down as a value field in a table, however I need to add this to a visual such as a pivot table or a bar graph, as seen in the image below (done on google sheets)
image
Looking for help on this, thanks!

2

Hi @rohit_SB,
What if you tried using the countOver function to assist here?

Alternatively, you could look in to the countIf function as well

Let me know if either of these options help with your case or if you have any additional questions.

3

I did try using the countOver function in the following method:
countOver(id, [userId])
However this didn’t work as well. I haven’t tried the countIf function yet but I can do that and report back to you!

4

@rohit_SB - I believe since you are looking to do it for a given user in current month then you need to first create a calculated column name Order Month from Order Date column and then utilize that in LAC expression. It would be something like

numberOfOrders = countOver(id, [userId], [orderMonth])

Then you can utilize that calculation in the expression to put it in the relevant bucket. Hope this helps!

2 Likes
5

Hi @rohit_SB

You can get your result with the following steps

  1. Create a calculated field that uses the Level Aware Calculation to count order by customer
    image

  2. Another calculated field for unique Customer Count
    image

  3. Create another calculated field that gets you your Bucket groups (something you already have)
    image

  4. Here’s the result

    image
    image695×374 23.8 KB

Regards,
Giri

3 Likes
6

Hi @sagmukhe Thank you, however this did not work. But I actually did find another use case for this formula!

7

This worked perfectly @Giridhar.Prabhu ! May I get the reasoning why a LAC-W function works well over here? Why do we use this function and what does this exactly do that allows me to recognize a calculated field as a dimension? I’d like to know as this might be extremely useful for future use cases.

8

Hi @rohit_SB

Here’s a nice article from @David_Wong. Hope this helps explain things in detail.

Regards,
Giri

2 Likes