Hello guys, how are you doing? Just back to the Pivot question.
I am trying to pivot rows to columns but, I want to do it inside my Analysis (avoiding ETL process). Basically, I am trying to do what we have in the follow picture.
Currently, I am using, SumOver + Ifelse+Prefilter to get the C3 rows. It is important to know that in my final analysis, I am filtering just C1 and C2, and then I am calculating Value/ValueC3 column.
where are you getting stuck on?
You can swap rows to columns.
If you are trying to filter some parts vs others I would look into aggregations and using the PRE_FILTER flag.
Thank you for your reply @Max . Really appreciate it.
Probably, I was not clear about what I need, and sorry about it.
I am not looking to switch rows to columns. The switch is just the first part of my process.
In the following picture, I give you an example of the result I need and a sample source.
Couple of things we need to consider:
Here is what I have when I try to use PRE_FILTER to switch (row to column)
Oct = sumOver(ifelse(Month=“Oct”,Value,0),,PRE_FILTER)
I hoped to have 18 in our Oct Column
Thank you again.
Are you able to get the correct value for the calculated field in your first step? To me, that looks like the most difficult part. How do you repeat the value for Oct for all the months of the year? It’s not an aggregation.
I think I got it.
Oct Total =
sumOver(ifelse(Month = "Oct", Value, 0), [Country], PRE_FILTER)
Month Total =
sumOver(Value, [Month], PRE_AGG)
Hi @David_Wong , thank you for your support. Almost there but when I select all countries, I did not have the correct result. I expected 27 (Oct Total) in the selection below.
USA - OCT 2022 - 9
CANADA - OCT 2022 - 9
FRANCE - OCT 2025 - 9
Thank you for your support. This is a challenging solution. It’s like I have to create a temporary table for each selection composition.
What makes this difficult is that you want to use PRE_FILTER so that filtering by Month (Jan, Feb and Mar) doesn’t impact your calculations but at the same time you want the Country and Year filters to be included.
In QuickSight you can’t use PRE_FILTER to ignore just 1 filter. I think you already saw the discussion there:
This post says the solution is to use the “in” function. I tried it and it seems to work:
First you have to use parameters for your Country and Year filters. Then rewrite the calculated fields as follows:
Month Total =
sumOver(ifelse(in(Year, ${Year}) AND in(Country, ${Country}), Value, 0), [Month], PRE_FILTER)
Oct Total =
sumOver(ifelse(Month = “Oct” AND in(Year, ${Year}) AND in(Country, ${Country}), Value, 0), , PRE_FILTER)
Hi @David_Wong thank you so much for your message. I really would like to solve this problem. It is crucial for us. Let me take a look and get back here.
Almost there… almost there…
the calculation is right, but when I select all of the available items, the calculation stops working.
There is something related to “All” selections.
comments, thoughts?!?
Thank you again.
It works when I select all the countries one by one but not when I select “All”. I think this has to do with the fact that when “All” is selected, it’s the reserved value which is assigned to the parameter.
You can read more about the reserved value here:
I tried to modify the calculated fields to account for the reserved value but it didn’t work.
I have never seen any demos showing how to use the reserved value, so it could be that I’m not using it correctly.
The workaround is to not show “All” in your controls to force users to select all values one by one.
Hi @David_Wong, how are you doing? Something wrong from my end. Even if I select all of them individually, I do not have a correct answer in this case.
They automatically checked our “All” option.
That’s weird. Here’s how I created my parameters, controls and filters. Maybe you created them differently.
Hi @David_Wong , thank you again for being so supportive. Yes, you are right. I created my parameters linked to a dataset field. I do not know whether it changes something or not.
I am trying to create a rule to treat when we select “All” but it has had no success until now.
Hi All, really enjoy the discussions here with ton of details and the exploration of different options. @woliveiras, the in() funciton is the closest and the most straightforward way. I know you are almost there expect the “all” problem - this is actaully a known limiation today with the parameter control “all” - since the current design is doing a no-op (meaning we are cancelling the filter, instead of passing all the elements from the control list). The team is aware of the issue and will work on it. Currently you will have to manually select the values, and I would suggest you hide the all option when you create the control - it will avoid confusing your user.
Let me know if it works, thanks!
Hi @emilyzhu,
What is a use case for the reserved value for “All”? I haven’t seen any demos or articles showing how to use it. It didn’t work when I tried to use it in this case. Do you mean that when the issue is fixed, we’ll be able to use the reserved value to solve this problem?
Hi @David_Wong , the reserved value for all is for different purpose, it is targeted for enable null for parameter control. Details you can find in this user doc
For the all issue I mentioned, it is the cuerrent design that is not compatible with the IN() function, essentially if you pass all value to the IN() function it will give you no data. We will try to fix that issue.
Thanks so much for the clarification @emilyzhu. Good question @David_Wong. @woliveiras Let us know if Emily’s solution/workaround works for you. This will help our community!
Hi all. Thank you @emilyzhu and @David_Wong . I think we have a great solution and I would like to accept the solution. I hope to be able to share what we have made here in the future.
We created an amazing solution using QuickSight.
