Trying to create a pivot table which aggregates the lifetime value of the opportunity based on the following:

1st: Per Sales Stage Name

2nd: Per Year Quarter (e.g. 2024 Q2)

Requirement in the pivot:

1. Only 1 unique Stage Entry Lifetime Value per Opportunity and per Sales Stage.
2. The Earliest Stage Entry Date per Opportunity and per Sales Stage is considered.
3. Duplicate line items (Opportunity || Sales Stage Name) should only have LTV (Lifetime Value) from the earliest date

FOR THOSE LOOKING FOR SOME CHALLENGE.

Sample pivot table:

Sample Data:

Pivot table result:

2 issues was encountered:

1. Duplicated Lifetime Values
2. For the Stage Enter Quarter, only the earliest Stage enter quarter should be considered:
   1. Opportunity number 123 - the $400 in 2024Q1 is correct vs the $800 from 2024Q1 and Q2 (duplicated)
   2. But since Opportunity number entered Stage 3 twice (2x), Lifetime Value for 2024 Q2 was added also, making the total to $800 instead of $400 only.
   3. Opportunity number 123 - the $300 in 2024Q1 is correct vs the $600 from 2024Q1 and Q2 (duplicated)
   4. But since Opportunity number entered Stage 3 twice (2x), Lifetime Value for 2024 Q2 was added also, making the total to $600 instead of $300 only.

Resolving the Issue#1: Duplicate Lifetime Values:

Using the "Stage Progression Active Flag" duplicate value is resolved by using this field in a calculation, using the following scenario using "CASE WHEN" sql statements:

1. If the Stage Enter date is latest date(has Y value) , Lifetime Value is added to the pivot table
2. If the Stage Enter date is not the latest date (has N value), Lifetime Value is 0.

Note: Lifetime value is also Y if the "Opportunity Number || Sales Stage name" line item is unique

Remaining issue:

Using the data in the first table, how to get only the line item with "earliest/first" stage enter date into the pivot table without having duplicate lifetime values?

====

Stage Entry Lifetime Value:

sum( CASE

WHEN "Sales Stage Name" = 'Stage 1'

and "Stage Progression Active Flag" = 'Y'

THEN "Table for Lifetime Value Stage 1"

WHEN "Sales Stage Name" = 'Stage 2'

and "Stage Progression Active Flag" = 'Y'

THEN "Table for Lifetime Value Stage 2"

WHEN "Sales Stage Name" = 'Stage 3'

and "Stage Progression Active Flag" = 'Y'

THEN "Table for Lifetime Value Stage 3"

ELSE 0 END)

==

Also added this filter:

"Stage Enter Date" = (min("Stage Enter Date" by ."Opportunity Number", ""Sales Stage Name" ) )

==

However, my understanding is that calculation comes first before the visualization filter. As a result, the filter has no effect on the results.

Please let me know your thought on this. Thank you!