Text column as values in Pivot table.

art_in_sky · December 4

I have a column that is based on calculation of ranges and identifies the risk category. I want to set that column as values in my pivot table , however it is not accepting the text column as values. How can I achieve this?

ColemenWilson · December 4

Text as values would make sense if you are doing a count of the text values. Here is an example:

Data_Devon · December 4

I agree with ColemenWilson.

You could also try to Cast() the values in a Beastmode to get them to integers/values.

Also, could edit it at the ETL level and transform the data column that way.

Should be lots of options - keep us posted on what works.

art_in_sky · December 4

@ColemenWilson @Data_Devon The reason I want to use text as values is because I have set up a column values as columns in pivot table, and i want to show color risk indicators that are based on the formula to see how they are spread across each row.

art_in_sky · December 4

However, it will only let me use the numeric values as values and not the text values.

ggenovese · December 4

The values section is meant for aggregation, because it is possible that when your Rows and Columns intersect that there may be more than one value present.

When you place your text field in the values section you're only provided with Count as the method of aggregation, however, if you create a beast mode with the value MAX(`Your Text Field`) then the pivot table will accept that as a valid method of aggregation and display the max text value.

ColemenWilson · December 4

I'm not sure I am completely understanding what you are trying to do. Could you create a table in a spreadsheet as an example?

art_in_sky · December 4

@ggenovese

This worked, thank you so much! Is there any way to color-code the cells based on the calculation or display the values when hovering over a cell in the Pivot Table? I can now show the text in the cell, but I also want to display the actual value and the range it falls under when hovered over.

ggenovese · December 4

Glad that worked for you! Pivot Tables do not provide a method for adding hover text so I think color coding using color rules may be your best option

art_in_sky · December 4

@ggenovese yes, the color rules are limited though.

art_in_sky · December 4

@ColemenWilson @Data_Devon

I wanted to pivot a column's values as columns and use text values from a Beast Mode calculation in the cells. Since Pivot Tables 'values' require aggregation, I applied MAX to my Beast Mode and it worked for me. Thank you so much for your assistance, and I apologize if my question was not articulated properly.

ggenovese · December 4

@Arthi Annadi - I'm not sure if any table cards provide hover text out of the box. You may want to look into DDX Bricks, or search the app store for custom pivot table apps.

Another option would be to pivot your data in an ETL so that you can use an HTML Card instead of a pivot table but this has limitations as well. In this thread I provide an example of an HTML Card with a growth indicator and hover text.

DavidChurchman · December 6

If you could simplify your table to two categorical dimensions, a Heatmap card (the one under Popular cards, not the Heatmap Table) can do what you're asking out of the box:

Pivots data
Can be easily and flexibly color coded
Has Hovertext

Text column as values in Pivot table.

Answers

Categories