Creating a dynamic pivot table where the value field can be controlled with a dropdown

Q&A
, , , , , , ,
1

Hi everyone! I’m new to Quicksight and wanted to know how I can create the a dynamic pivot table where the value field can be adjusted dynamically using a drop down menu selection. For some details, I have a pivot table with the company name and company ID in the rows. The column consists of the months. The values field must display the revenue, etc. However, I have roughly 15 values to show and that would increase the size of the table by a lot, so to reduce that, I’m looking to create a drop-down menu that would allow users to select whatever dimensions they need to compare. Since I’m new, could anyone provide a breakdown of how this can be achieved? I’ve looked through other QnA’s but did not completely understand the steps. Thank you!!

2

Welcome @rohit_SB
check out the demo central there is sample for your case. Not pivot table but logic will be the same.

https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Interactivity-Dynamic-Dimensions-and-Measures

BR

3

Hi @rohit_SB,
We hope the reply from @ErikG worked for you.
If it helped, please help the community by marking the reply as “Solution”, but let us know if this is not resolved. Thanks for posting your question on the Quick Sight Community Q&A Forum.
Andrew

4

Thank you @ErikG @abacon . I think this makes sense. Sorry I haven’t had the opportunity to touch base with this project in the last few days but the solution Erik posted makes sense logically.

5

Hi,
I followed the demoncentral example, I want to build a dropdown list instead of radio box. I have 4 dimensions in the dropdown: Year, Month, Manager and Language. Then I create calculated filed like below, but I got ifelse() syntax error.

ifelse(
${pDimension} = “Year”, {year_id},
${pDimension} = “Month”, {month_id},
${pDimension} = “Manager”, {manager},
${pDimension} = “Language”, {cust_language},
NULL)

If I keep only 2 conditions, it will work.
ifelse(
${pDimension} = “Year”, {year_id},
${pDimension} = “Month”, {month_id},
NULL)

I can’t figure out why it is happening. Appreciate if you have any clue.

6

Hello @Shou-Li_Hsu !

The original topic in this thread has been solved, but I want to make sure that your question is prioritized by the community experts.

Could you please re-post your question as a new topic at the top of the community so that it will be a priority for the experts? You can link back to this topic as well to give more context to your question.

7

HI @Shou-Li_Hsu , the issue you’re facing is due to the nature of the ifelse function. Ideally, your ifelse function would have n number of if statements, followed by an else statement at the end.
So i see you’ve already created your parameter, which is step 1. Step 2 would be creating a control for that parameter (such a dropdown), which i believe you’ve already done as well.
The next step is creating the calculated field. Here, you have an ifelse case. So in your scenario with the 4 possibilities, the way you would go about it is this:

ifelse
(
  ${pDimension} = “Year”, {year_id},
  ${pDimension} = “Month”, {month_id},
  ${pDimension} = “Manager”, {manager},
  {cust_language}
)

In the second scenario, where you only have two choices, think of the ifelse structure as the first statement being an if, the second one being an else.

ifelse
(
  ${pDimension} = “Year”, {year_id},
  {month_id}
)

This method has worked for me throughout and I can even provide some examples to you:
ifelse(

    ${dateAggregation}='Day', maxOver(dailyMenuForDateEnd, [truncDate('DD', dailyMenuForDateEnd)], PRE_AGG),
    ${dateAggregation}='Week', maxOver(dailyMenuForDateEnd, [truncDate('WK', dailyMenuForDateEnd)], PRE_AGG),
    ${dateAggregation}='Month', maxOver(dailyMenuForDateEnd, [truncDate('MM', dailyMenuForDateEnd)], PRE_AGG),
    ${dateAggregation}='Quarter', maxOver(dailyMenuForDateEnd, [truncDate('Q', dailyMenuForDateEnd)], PRE_AGG),
    maxOver(dailyMenuForDateEnd, [truncDate('YYYY', dailyMenuForDateEnd)], PRE_AGG)
)

The above code is for a dropdown menu that allows me aggregate data on the x axis dynamically. so it allows me to view things from a daily, weekly, monthly, quarterly or yearly view. Notice how the last part (the else statement), is just stating the field itself.

8

Thank you for kindly reply.
With various tries and researches, I managed to figure it out. It turns out that all return value should be the same data type. In my case, year_id and month_id are numbers, but manager and cust_language are strings. I convert year_id and month_id to string, then it works.

Thanks for all your help