Calculated field nesting error

Question & Answer
, , ,
1

Hello,
I have this expression -

sum(
min(
ifelse(
{Current Life cycle week} <= {week num},
minOver({flowers ratio expected}, [{crop type}, {week num}], PRE_AGG) *
minOver({Current growing plants},[{batch id}], PRE_AGG) /
ifelse({flower size} = ‘L’ OR {flower size} = ‘M’, 5, ifelse({flower size} = ‘S’, 20, 0)),
0
),
[{Expected week date}, {crop type}, {batch id}]
)
)

I need to add to this expression a minimum function group by [{Expected week date}, {flower size}], but I receive a nesting error. How can I solve it/ write this calculated field differently?

Thanks in advance,
Mia

2

Hello @miap !

Can you try this:

sumOver(
minOver(
ifelse(
{Current Life cycle week} <= {week num},
minOver({flowers ratio expected}, [{crop type}, {week num}], PRE_AGG) *
minOver({Current growing plants},[{batch id}], PRE_AGG) /
ifelse({flower size} = 'L' OR {flower size} = 'M', 5, ifelse({flower size} = 'S', 20, 0)),
0
),
[{Expected week date}, {crop type}, {batch id}], PRE_AGG),
[]. PRE_AGG)

I would also check out this blog post:

3

This expression is correct but only as long as the field ‘flower size’ is in the table-

minOver(
sum(
min(
ifelse(
{Current Life cycle week} <= {week num},
minOver({flowers ratio expected}, [{crop type}, {week num}], PRE_AGG) *
minOver({Current growing plants},[{batch id}], PRE_AGG) /
ifelse({flower size} = ‘L’ OR {flower size} = ‘M’, 5, ifelse({flower size} = ‘S’, 20, 0)),
0
),
[{Expected week date}, {batch id}]
)
),
[{Expected week date},{flower size}]
)

I want to use this calculated field in a table with only ‘Expected week date’ . Is it possible?

4

Hello @miap !

I’m sorry for the late response, I was OOO. Would it work to hide the flower size column in your table visual?

The process below works for tables and pivot tables:

1 Like
5

Hello @miap !

Are you still working on this issue or were you able to find a solution? Again, I would recommend just hiding the column.

6

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!