Pivot table conditional formatting error

Q&A
,
1

name/carriernames tracking_number
1.John

CANADAPOST 3

LME_WORLD 1

2.Bob
fedex. 3

GORI. 1

dhl 2
It is a sample pivot table each name has multiple carrier names and its package count
i am trying to find overall package count percentage of tracking number using countdist(tracking_number)

distinct_count({tracking_number}) /
distinctCountOver({tracking_number}, [{name}], PRE_AGG) * 100

tried using this formula but it did not work so used table calculation with percentage total group down option

now i need to make a conditional formatting for canadapost if it is the highest value carrier name for each name

but it is not helping with this table calculation

2

Hi @Dafini,

My initial thought would be to start by creating some type of denserank calculation to rank the carriers’ highest values per name/person.

Then you can try building an ifelse statment that checks for rank of 1 in above calc. and carrier = 'Canadapost’.

Let me know if this type of work around could work for your scenario or if you have any additional questions.

3

i tried this but this gives me this error-Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

4

Hi @Dafini,

Hope everything is well! Were you able to find a workaround to your question since your last post or are you still facing persistent issues regarding the pivot table? If we do not hear back in the next 3 business days, I’ll close out of this topic.

Thank you!

5

Hi, thanks for following up! I’m still running into issues with the conditional formatting piece when trying to highlight the highest-value carrier (like CanadaPost) per person. The aggregation mismatch error is still blocking me. Any suggestions on how to restructure the calc would be super helpful.

6

Hi @Dafini,
Apologies for missing your last response. So I went back and did some further testing to try and work around the aggregation errors. While the sample data is a bit different, I tried to replicate the scenario as best I could with what I have. You can take a look at the Arena view below where I created (once you click on the link, make sure to hit the ‘copy analysis’ button on the left side so that you can gain access to the analysis and see the work that went into this).
Pivot table conditional formatting error

Since QuickSight does not allow you to combine aggregated and non-aggregated fields, we had to find a way to aggregate the carrier name(s)…or in my example, the country names.

I started with creating fields for the total count per region (for you would be per name/user) and then another one for overall total count:

Per Region:
distinctCountOver({Order ID}, [Country], PRE_AGG)

Total Count:
distinctCountOver({Order ID}, [Region], PRE_AGG)

For the percentage, I know you said you switched over to table calculations but here’s the syntax I used to get the percentages on their own:
sum({Count Per Region})/sum({Total Count})

Next, I built a rank for my countries per region. So for you, this would be to rank the carriers per name/user)
denseRank(
[max({Count Per Region}) DESC],
[Region]
)

The last part was the tricky part as we need to figure out a way to aggregate your carrier field so that it can be utilized in the conditional formatting ifelse. To try and stay away from using other fields to aggregate (since I don’t know what your entire dataset looks like), I used the strlenfunction and placed inside a max aggregation so that it provided me with a metric I could use to filter carriers…(in my example, I substituted ‘United States’ for what your ‘CanadaPost’ would be):
max(strlen(Country))

This gave me a unique value of 13 for ‘United States’ as the space in between counts as well. This is where it will be dependent on your carrier options, ideally no other carrier will have the same string length as ‘CANADAPOST’ so that it’s the only one that produces ‘10’. Last step, I setup my ifelse statement to use in the conditional formatting:
ifelse({Country Rank Per Region} = 1 AND {Max String Length} = 13, 1, 0)

So if my country has the highest count and the string length = 13 (which would be ‘United States’), the apply a 1 else 0.
So for the conditional formatting, I setup the column I’d like to highlight, but base it on the conditional formatting calculation and have it color the box if that equals 1. Since this is in a pivot table, make sure you apply to ‘subtotals’ in the ‘Condition’ section.

image
image250×496 17.2 KB

In my example, I created two tables, the second one I left all the additional testing fields in there so that you can see the expanded logic.

Let us know if this could work for your scenario or if you have any additional questions

7

Hi @Brett thanks for your reply and explanation. however in my case country_names repeat for every other region in the table, so it calculates overall count. in your example region has distinct countries but my problem has repeating names so china would occur in 2 regions with the same count, is there any other way?

8

Hi @Dafini,
You’ll have to add an additional field into your dimension space then.
I transitioned my example over to industry instead of country (as industries apply to all regions unlike countries do).

Per Region:
distinctCountOver({Order ID}, [Industry, Region], PRE_AGG)

Total Count should stay as is

Percentage:
sum({Industry Count Per Region})/sum({Total Count})

New Rank:
denseRank(
[max({Industry Count Per Region}) DESC],
[Region]
)

Let me know if you have any additional questions

9

Hi @Dafini

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

10

Hi @Dafini

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!