Calculation between two

[kenphamvn]

Dear All

i want to calculation sum of values from two date columns

Here is my sample data

UserID	CutOffDate	TransDate	Amount
User01	3/12/2018	3/10/2018	10
User01	3/12/2018	3/11/2018	10
User01	3/12/2018	3/12/2018	10
User01	3/12/2018	3/13/2018	10
User01	3/12/2018	3/14/2018	10
User01	3/12/2018	3/15/2018	10
User01	3/12/2018	3/16/2018	10
User02	3/15/2018	3/14/2018	10
User02	3/15/2018	3/15/2018	10
User02	3/15/2018	3/16/2018	10

i want to get Sum of Amount between Cutoffdate And Max selected Transdate for each UserID

Example:

if no Transdate selected, it mean Max(TransDate) =   '3/16/2018'

here my expected result

UserID	Sum Amount	Description
User01	50	Sum of Amount from Cutoffdate of User01 (3/12/2018) to Max(TransDate) ('3/16/2018')
User02	20	Sum of Amount from Cutoffdate of User02 (3/15/2018) to Max(TransDate) ('3/16/2018')

if selected Transdate ='3/14/2018'

here my expected result

UserID	Sum Amount	Description
User01	30	Sum of Amount from Cutoffdate of User01 (3/12/2018) to Max(TransDate) ('3/14/2018')
User02	0	Because Cutoffdate > Max(TransDate)

but i am not sure how to write expression correctly.

Any help would be appreciated.

Thanks in advanced

An Pham

[rubenmarin]

Hi An, check this: Set analysis by field in other table

To resume: Create a flag in script and use the flag in set analysis

Updated solution without changing the model:  SUM({<AccountIndexKey={"=TradeDate>=CutOffDate"} >}Amount)

[Anonymous]

Hi,

may be like below,

=aggr( sum({<TranDate={">=$(p(=date(min(CutOffDate))))<=$(Date(Max(TranDate))) "}>}Amount),UserId)

[sumanta12]

Hi Pham,

Please find the attached application.