Can I have Tableau (Include,Exclude, fixed ) LOD in one calculated field on QuickSight?

{exclude [cost_center], [job_function_roll_up], [JobFunction]: sum({FIXED [NewScenario], [snapshot_date], [Prod Group], prod_type, [cluster_name]: MAX(if [NewScenario] = [Scenario 1] and year([snapshot_date]) = [Scenario 1 Year] and ([Prod Group] = ‘AMER’ or [Prod Group] = ‘EMEA’ or [Prod Group] = ‘APAC’ or [Prod Group] = ‘Security’) then {exclude [cost_center], [job_function_roll_up], [JobFunction]: sum(zn([wtke (Custom SQL Query3)]))} else NULL end)})}

I don’t believe you can do an exclude. The only way to do it would be to partition by all other fields. Include you can do that by partitions.

Fixed is similarly to partitions.

Do you have an example of how you data should look? Might be easier for me to understand if you can do it in a calculated field.

Hi @Max,

From the screenshots attached, I want to calculate ‘S1 TKE B1’ . The dimension on the visual can be [Org Rollup], [Prod Group], [Productivity Grouping], [cost_center],[job_function_roll_up], [JobFunction],[FTI Check].
These all dimensions are mentioned in the hierarchy format and can be expanded or shrink based on the user selection.
Irrespective of what the USER select the calculation should exclude all the dimension mentioned above and calculate sum of ‘WTKE’ based on fixed dimension [NewScenario], [snapshot_date]. These fixed dimensions are present in the visual.
Also,[NewScenario] in itself a calculated field : IF [CE] = ‘Yes’ then ‘CE’ else [scenario] END

Tableau Calculated Field:
{EXCLUDE [Org Rollup], [Prod Group], [Productivity Grouping], [cost_center],[job_function_roll_up], [JobFunction], [FTI Check]: SUM({FIXED [NewScenario], [snapshot_date]: MAX(if [NewScenario] = [Scenario 1] and year([snapshot_date]) = [Scenario 1 Year] then [wtke] else 0 end)})}

Hello @rawatprr, I believe Level Aware Aggregation calculation can be leveraged in this case. Please see the below snapshot for the details where I have tried to replicate your scenario. It is ignoring all the dimensions except Segment for this example. Let me know if this helps/works for you!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

1 Like

Hi @sagmukhe, Can I have ‘sum of sales by segment’ even if dimension ‘Segment’ is not mentioned in the visual and it exclude rest all other dimensions?
I think on QuickSight there is a limitation: It will not work unless ‘Segment’ is included in the visual and the granularity ‘Segment’ has to be explicitly specified whereas exclude just ignores the chosen dimension(s).

1 Like

Hi @sagmukhe

Can I use ‘SegmentSales’ from your example/ ‘S1 TKE B1’ from my example as an input for calculation of another Calculated field? Moreover, during this scenario I don’t want to show ‘SegmentSales’/ ‘S1 TKE B1’ in the visual.

In the screenshot mentioned below, I need to calculate ‘Scenario1 WTKE’ which is using ‘S1 TKE B1’

Yes, there’t not a direct mapping between the functionality is what I have also observed. I am just trying to figure out an alternative solution. For example, you can hide the Segment column to get almost an equivalent output. Let me know if that helps!

1 Like

It depends. There are some restrictions with respect to using an already created LAA fields within another one as it will not allow the aggregation. However there are definitely calculations that you can do. For Example , the following is a valid calculation.

1 Like

Hello @rawatprr - If the suggestions helped you to solve the query, would request to mark the relevant post as Solution for the benefit of the community! Thank you!

Hi,

We have recently also released level aware calculations with dynamic grouping : Create advanced insights using level-aware calculations in Amazon QuickSight | AWS Big Data Blog

2 Likes