LAC-W based calculated field in pivot

Question & Answer
, , , , , , ,
1

Hi team, is there any workaround to use LAC-W functions as dimensions in a pivot?

I have duplicate values in my dataset due to differences in granularity of the joined tables in custom SQL, I want to

Step-1: Remove duplicates using LAC-W function avgOver
Step-2: Use percentileRank function to categorize the distinct values into 10 buckets
Step-3: Use the percentile field as row/column in a pivot to look at its distrubition

Any help on this is hugely appreciated, thanks in advance

2

Hi @sssaahi, welcome to the QuickSight Community, to address your issue of using LAC-W functions as dimensions in a pivot, you can follow these steps:

Step 1: Remove Duplicates Using avgOver

First, you need to remove duplicates using the avgOver function. Here’s how you can achieve this:

**Create a Calculated Field to Handle Duplicates:**Use the avgOver function to create a calculated field that averages your measures over the dimensions causing duplicates.

avgOver({measure}, [{dimension1}, {dimension2}, ...])

For example, if you have a measure Sales and dimensions Product and Date:

avgOver(Sales, [Product, Date])

This calculated field will average the sales values, effectively removing duplicates caused by different granularities.

Step 2: Categorize Distinct Values into Percentile Buckets

Next, use the percentileRank function to categorize the distinct values into 10 buckets:

  1. Create a Calculated Field for Percentile Rank:
percentileRank([dimension], [measure])

For example, to calculate the percentile rank of sales for each product:

percentileRank([Product], Sales)
  1. **Convert Percentile Rank to Buckets:**Create another calculated field to convert the percentile rank into buckets. Assuming you want 10 buckets:
CASE 
  WHEN percentileRank <= 0.1 THEN '1'
  WHEN percentileRank <= 0.2 THEN '2'
  WHEN percentileRank <= 0.3 THEN '3'
  WHEN percentileRank <= 0.4 THEN '4'
  WHEN percentileRank <= 0.5 THEN '5'
  WHEN percentileRank <= 0.6 THEN '6'
  WHEN percentileRank <= 0.7 THEN '7'
  WHEN percentileRank <= 0.8 THEN '8'
  WHEN percentileRank <= 0.9 THEN '9'
  ELSE '10'
END

Step 3: Use Percentile Field in a Pivot Table

Finally, use the percentile field as a dimension in your pivot table to look at its distribution:

  1. Create the Pivot Table:
  • Drag the percentile bucket field (created in Step 2) to the Rows or Columns of your pivot table.
  • Drag the measure (e.g., Sales) to the Values area.
  1. Customize the Pivot Table:
  • Adjust the pivot table to show the distribution of sales across the percentile buckets.

Example Summary

Here’s a summary of the steps:

  1. Calculated Field to Remove Duplicates:
avgOver(Sales, [Product, Date])
  1. Calculated Field for Percentile Rank:
percentileRank([Product], Sales)
  1. Calculated Field for Percentile Buckets:
CASE 
  WHEN percentileRank <= 0.1 THEN '1'
  WHEN percentileRank <= 0.2 THEN '2'
  WHEN percentileRank <= 0.3 THEN '3'
  WHEN percentileRank <= 0.4 THEN '4'
  WHEN percentileRank <= 0.5 THEN '5'
  WHEN percentileRank <= 0.6 THEN '6'
  WHEN percentileRank <= 0.7 THEN '7'
  WHEN percentileRank <= 0.8 THEN '8'
  WHEN percentileRank <= 0.9 THEN '9'
  ELSE '10'
END
  1. Create the Pivot Table:
  • Use the percentile bucket field as a row or column.
  • Use the de-duplicated measure (from Step 1) as the value.

By following these steps, you can successfully use LAC-W functions as dimensions in a pivot table.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

3

Hi @Xclipse,

Thanks for your response.
I followed these steps, but I’m getting an error in Step-3 while adding percentile bucket field in pivot rows/columns - Custom aggregation field is not allowed as a dimension

PFB my data sample
order, brand, region, company, revenue
O1, B1, R1, C1, 1000
O2, B1, R1, C1, 1000
O3, B2, R1, C1, 1000
O4, B2, R2, C2, 500
O5, B3, R2, C2, 500

I want to create percentile buckets based on revenue at company level and show the distinct count of companies across the percentile buckets.

Below are the calculated fields I’ve created

  1. revenue = avgOver(coalesce({revenue},0), [{company},{region}],PRE_AGG)
  2. decile = ceil(percentileRank( [{revenue} ASC], [{region}])/10)+1
  3. decile_dimension =
    ifelse({decile}=0, 0,
    ifelse({decile}=1, 1,
    ifelse({decile}=2, 2,
    ifelse({decile}=3, 3,
    ifelse({decile}=4, 4,
    ifelse({decile}=5, 5,
    ifelse({decile}=6, 6,
    ifelse({decile}=7, 7,
    ifelse({decile}=8, 8,
    ifelse({decile}=9, 9,
    ifelse({decile}=10, 10,NULL)))))))))))

Please let me know if I’m missing anything here. Thanks in advance.

4

Hi @Xclipse,

I believe percentileRank is the function causing the error. Can you please help with any work-around? Thanks in advance.

5

Hi @sssaahi, please can you create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena

6

Hi @sssaahi,
It’s been awhile since we last heard from you, are you still in need of assistance or were you able to find a work around? If you’re still looking for further assistance, please follow Xclipse’s note to create a QuickSight Arena so that we can look into this a bit further.

If we do not hear back from you within 3 business days, I’ll go ahead and archive this topic. If you have any further questions past that, feel free to create a new topic for discussion and link this page for relevant information.

Thank you!

7

Hi @sssaahi,
Since we haven’t heard back from you, I’ll go ahead and archive this topic. However, if you have any lingering questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!