# Pivot Rows and calculations

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:

• We have dynamic filter selections.
• The table result is filtered by JAN, FEB, MAR (Oct is not there)
• The switching row to the column is Oct (technically this calculation is dependent on the filter selections)

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.

1 Like

I think I got it.

Oct Total =
`sumOver(ifelse(Month = "Oct", Value, 0), [Country], PRE_FILTER)`

Month Total =
`sumOver(Value, [Month], PRE_AGG)`

1 Like

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.

1 Like

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)

2 Likes

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.

1 Like

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.

2 Likes

Thank you @David_Wong let me see and I will be back soon.

1 Like

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.

1 Like

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.

1 Like

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!

2 Likes

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?

1 Like

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.

1 Like

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.