Trying to create a pivot table which aggregates the lifetime value of the opportunity based on the following (using the earlies stage enter date)

1st: Per Sales Stage Name

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

Sample pivot table:

Sample Data:

Pivot table result:

2 important notes:

1. Only Stage Entry Lifetime Value per Opportunity and per Sales Stage.
2. The Earliest Stage Entry Date per Opportunity and per Sales Stage is considered.

2 issues was encountered:

1. Duplicated Lifetime Values
2. For the Stage Enter Quarter, only the earliest Stage enter quarter should be considered:
   1. For the case of Opportunity number 123, only the $400 in 2024Q1 should be considered. 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.
   2. For the case of Opportunity number 456, only the $300 in 2024Q1 should be considered. 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 Custom calculation:

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 to include only line item with minimum stage enter date:

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

As a result, the filter has no effect on the results.

Please help provide suggestion on how we can go about this. Thanks!