Pivot table error after applying conditional formatting to Total column

Q&A
, ,
1

I am getting the error message, “Please contact the Qlik team to resolve the issue,” as soon as I apply %Total_Rank to the Total column in the pivot table. The conditional formatting is intended to identify the highest and second-highest total percentages. I was able to apply the same conditional formatting successfully to all the monthly columns from January through December. However, when I apply the conditional formatting to the Total column, I receive the above-mentioned error.

Total = January + Febraury + March + April + May + June + July + August + September + October + November + December

%Total = sumOver(Total,\[{date_sold_stock}\])
/
sumOver(Total)

%Total_rank = ifelse(
isNull({%Total}),
3,


rank([{%Total} DESC])=1,
1,

rank([{%Total} DESC])=2,
2,

3


)

First image: It is the error after applying the conditional formatting.

Second image: the entire pivot table before applying conditional formating to the Total column

quicksight_error_1
quicksight_error_11669×886 68.8 KB

2

Hi @Lal_Prasanth_Paulraj and welcome to the community!

Thanks for providing so much detail. I recreated this case in my environment and got the same error message that you did. My guess would be that this has something to do with the rank function we’re both using. The reason I say this is that rank() is a table calculation, and table calculations need the proper field context from the visual’s field wells to resolve. When used in conditional formatting (rather than as a displayed value), Quick Sight may not have the full evaluation context it needs, which is what’s triggering the error.

Unfortunately, I wasn’t able to find a workaround that allows rank() to work within conditional formatting on a pivot table. Since you already used the correct ‘error’ tag, I would recommend opening a support ticket (Case management - AWS Support) and flagging this as a bug, since the error message itself (“Please contact the Quick team”) suggests this is a known limitation.

Hope this helps and feel free to keep us updated!

3

Hi @Lal_Prasanth_Paulraj,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime. Moreover, if you submitted a support ticket and it uncovered any findings, please feel free to share them!

If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you.