Hiding unused (calculated) columns

cgreenacre · May 12, 2023, 6:08pm

Is it possible on a free-form or classic visual using a pivot table to show/hide the four columns in the group by field well? For example, if the user selects “none” in a control, then the appropriate column is hidden.

For example, we have these four controls that drive what field is shown in the pivot area. (The four columns in the group by field well are col1, col2, col3, col4, which are calculations based on the response by the user via parameters) And as you can see, the user may not want to use all four controls.

This is what is returned

What we would like to do is hide the two columns titled None, when the user selects “None” in the 3rd and 4th control.

In this case, when the second, third, & fourth controls are set to None, we would like to hide the three columns titled None.

I’ve looked at free-form, and you can hide/show visuals based on parameters, and I was thinking about four visuals, one with just one column, the second with two columns, the third with three columns, and the fourth with four columns; thus I would show the first visual if the second, third, & fourth controls are set to None, but the rules section only allows you use one parameter when displaying a visual. The rules don’t allow you to use calculated fields.

Here’s the definition of col2, the second column in the group by field well. The definitions of col1, col3, & col4 are similar–the business logic is to allow the user to select which columns they want to see and in what order those columns appear.

The goal of this question is to hide the "unused: columns (where the user selected None as a response for the corresponding control) of the “group by” field well.

Am I overlooking a simpler solution?

Max · May 15, 2023, 9:11pm

Hi @cgreenacre

To show the certain visual can you say that the ${col2} does not equal None.

And then you show your visual.

After that you should be able to have your table reference the calculated field you created.

Does that make sense / help?

cgreenacre · June 2, 2023, 11:51am

@Max We will try to use your approach. I think we will have to use four different visuals each hiding each other, so that visual with one column is the farthest back and the visual with 4 columns is on top. Then set up the rule as you have described should work. Will let you know.

cgreenacre · June 23, 2023, 12:49pm

We have it working with the four visuals approach. Our users like it. I have to give a presentation next week on how I built it in the analysis, giving it to other authors at my company. I should have a good outline of the steps involved, and can share that outline here.

cgreenacre · June 30, 2023, 7:50pm

This is a set of instructions to create a pivot table with a variable number of columns. This is a non-trival exercise, in that it uses the following pieces of QuickSight
parameters:
calculated columns
controls
Format visual
Settings > Free-form visuals with overlapping visuals (you should already be comfortable working with the Free-Form visuals)
Format Visual > Totals > Pin Totals
Format Visual > Title
Format Visual > Row Names
Format Visual > Rules

When I did built this as a demo in front of about 30 authors at my company, the process took almost 40 minutes, including making mistakes and correcting them.

I work in the hospitality industry, so the terminology in this how-to is based on that; this technique should work in any industry.

I will be describing measures (values field well) and attributes (rows field well) that we use in the hospitality industry, and how that plays a part in this dashboard.

The hospitality industry is a lot like the airline industry. If a plane takes off with an empty seat, that’s lost revenue. If a room is empty overnight, that’s lost revenue.

The hospitality industry monitors reservations for rooms. The measures associated with a given reservation are revenue and the number of room-nights (number of rooms multipled by number of nights). The hospitality measure or KPI named Average Daily Revenue is based on these two measures. The attributes associated with a reservation include the following:
booking date (when the guest booked the reservation)
arrival date
departure date
Active vs Cancelled (did the guest cancel the reservation)
Rate Code (do you belong to AAA or AARP, the frequent guest program, Group rate for a one time event, and lots of other codes)
Room Category (King or two Queens, etc)
country of origin of the guest
booking source of the reservation (did you booking through an our website, expedia, an airline website, through our 800 toll free number, etc)
Which Travel Agent did you use
Is there a Corporate Account on the reservation?

The goal here is to build a pivot table with last two columns being the measures/values, and using controls, the user can define the number of columns and what attributes are displayed in the first (upto) four columns.

Step 1, build a dataset with all of this data
Step 2, create an analysis, and a pivot table visual to that analysis and add Values field well the two measures.
Step 3, create a parameter named pCol1, that allows a single value
Step 4, create a control, fill the “hide all” check box, with a style of List. The list being your attribues (as text). Our attributes were the following list:
Booking Date (YYYYMM)
Arrival Date (YYYYMM)
Departure Date (YYYYMM)
Active vs Cancelled
Rate Code
Room Category
Country of Guest
Country of Hotel
Booking Source
Travel Agency (IATA nbr)
Corporate Account nbr
Step 5: go back to your parameter pCol1, edit it to add one of the above values as your default value.
Step 6: create a calculated column named col1 that is an ifelse on your parameter pCol1 and that translates an entry in your control to a field in your dataset. It could look like this:
ifelse(
$(pCol1}=‘Booking Date (YYYYMM)’,{Booking_Dt},
$(pCol1}=‘Arrival Date (YYYYMM’,{Arrival_Dt},
$(pCol1}=‘DepartureDT (YYYYMM)’,{Departure_Dt},
$(pCol1}=‘Active vs Cancelled’,{ActiveCancelFlg},
${pCol1}=‘Rate Code’,{rate_code_desc},
${pCol1}=‘Room Category’,{room_category},
$(pCol1}=‘Country Of Guest’,{GuestCountry},
${pCol1}=‘Property Country’,{property_country_name},
${pCol1}=‘Booking Source’,{perfstat_booking_source},
${pCol1}=‘Travel Agency’,{ta_string},
${pCol1}=‘Company’,{ca_string},
‘’)
Step 7: add this new column col1 to your Rows field well
Step 8: to make the column title change, click the pencil icon for the visual (Format Visual panel appears) and open Row Names, click the ellipse beside col1 and insert then entry <<$pCol1>>
Step 9: test the visual at this time, when you select in entry in the control, then the data in the first column should change, as well as the title for that column
Step 10: go back and edit your parameter pCol1 so that one of the entries is step 4 is the static default.
Step 11: repeat steps 3 through step 9 (not 10) and using pCol2 and col2 instead of pCol1 and col1.
Step 12: edit the pCol2 control and and at the top of the list add the entry ’ None’ (without the single quotes but with the leading space). The leading space forces this entry to be the first entry in the list.
Step 13: edit the paramater pCol2 so that the default is ’ None’ (again without the single quotes but with the leading space)
Step 14: test, if you have two measures (entries in your Values field well), you should have four columns in the pivot table, the second column has a title of None, and there is no data in the second column. When you select a different value in your second control, the second column should have data. You may need to expand the first column. Note: We have NOT YET applied the logic to hide this second column when the selected entry in the control is None.
Step 15: repeat steps 3 through 9 using pCol3 instead of pCol1 and col3 instead of col1
Step 16: repeat steps 12 through 14.
Step 17: repeat steps 3 through 9 using pCol4 instead of pCol1 and col4 instead of col1
Step 18: repeat steps 12 through 14.
At this point you have an pivot table visual with six columns of data, four attributes (col1 through col4 in the Rows field well, and two columns of data in the Values field well).
Step 19: Using the pencil icon for the visual, which brings up the Format Visual panel, open the Total section, and clear the check box for Pin Totals. Doing this now prevents a visual problem that occured for me. When I didn’t know about this, I would end up with a variable number of total lines (with correct values) after I completed the next section. It was as if the total lines were “bleeding upwards” like a “soiled bandaid”. I don’t know if this is a bug or not, but I did discuss this with AWS QS tech support on a chime call. We stumbled across this solution by just trying things.

Now we are going to hide the unused columns, the ones that have None as a title.
Step 20: on Settings (left most panel at the bottom, rarely used) change Sheet Layout from either Tiled or Classic to Free-form. Remember to click the Apply button (scroll down to find it)
Step 21: add a subtitle to visual, such as “nbr columns 4”. We will be changing this 4 to 3 or 2 or 1 later on. This will be serving as a breadcrumb so we know which one of four visuals we are viewing.
Step 22: Duplicate the visual to the same sheet using the ellipses > Duplicate Visual > same sheet command.
Step 23: change the subtitle to be “nbr columms 3”
Step 24: remove col4 from the field well
Step 25: move this new visual so that it exactly overlaps (covering) the original visual.
Step 26: using the visual’s ellipses, use “send to back”. Don’t click on anything–the bread crumb should say #4, and you will see six columns, but if you look at the rows section of the field well, it will shows just three columns. I don’t know if this is a bug or not, but the workaround is to click on the visual to highlight it. As you do this, you will see the col4 appear in the Rows field well. Now you can work on the topmost visual.
Step 27: repeat steps 22 through 26 to create a third visual with bread crumb #2, removing col3 from the rows field well, and just cols 1 & 2 in the rows field well.
Step 28: repeat steps 22 through 26 to create a fourth visual with bread crumb #1, removing col2 from the rows field well and just col1 in the rows field.

Step 29: At this time you should have four visuals that exactly overlap each other.
Also, bread crumb #4 should be visible, and after clicking on the visual the rows field well should have four columns in it.
Now we are going to verify that the breadcrumbs are in the proper sequence, with 4 on top of 3, which is on top of 2, which is on top of 1.
Send breadcrumb #4 to the back. Click on the visual with breadcrumb #3, you should see 3 columns (col1, col2, col3)
Send breadcrumb #3 to the back. Click on the visual with breadcrumb #2, you should see 2 columns (col1, col2)
Send breadcrumb #2 to the back. Click on the visual with breadcrumb #1, you should see 1 column (col1)
Send breadcrumb #1 to the back. Click on the visual with breadcrumb #4.

If anything is not in the proper sequence, use a combination of send to back and send backwards to get the visuals in the proper sequence of 4, 3, 2, 1. This is important before proceeding.

Step 30: At this time you should be on breadcrumb #4 with four columns in the visual and in the rows field well. Click on the pencil for the visual to bring up the Format Visual panel. Open the Rules section, Move the slider from left to right to “Hide this visual by default”. The visual will say “Hidden based on rule”. Also add a rule that says Show if pCol4 does not equal None (and don’t forget about the leading space before None). Send this visual to the back.
Step 31: Click on visual with breadcrumb #3, which will show the three columns in the Rows field well. Click on the pencil for the visual to bring up the Format Visual panel. Open the Rules section, Move the slider from left to right to “Hide this visual by default”. The visual will say “Hidden based on rule”. Also add a rule that says Show if pCol3 does not equal None (and don’t forget about the leading space before None). Send this visual to the back.
Step 32: Click on visual with breadcrumb #2, which will show the two columns in the Rows field well. Click on the pencil for the visual to bring up the Format Visual panel. Open the Rules section, Move the slider from left to right to “Hide this visual by default”. The visual will say “Hidden based on rule”. Also add a rule that says Show if pCol2 does not equal None (and don’t forget about the leading space before None). Send this visual to the back.
Step 33: Click on visual with breadcrumb #1, which will show the two columns in the Rows field well. Click on the pencil for the visual to bring up the Format Visual panel. Open the Rules section, Move the slider from left to right to “Hide this visual by default”. The visual will say “Hidden based on rule”. Also add a rule that says Show if pCol1 does not equal None (and don’t forget about the leading space before None). Send this visual to the back.
Step 34. Click on the visual with breadcrumb #4. You should see the four columns in the rows field well.
Step 35. Pubish the analysis to a dashboard.
Step 36. Thoroughly test the controls on the dashboard. If issues fix the analyis and republish.

Note, in the dashboard if a user selects an entry in first control, selects None in the second control, and selects an entry (other than None) in the third control, and selects None in the fourth control, then the dashboard will shows three columns. I haven’t figured out the rules to appropriately handle this situation.