Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Hi,
may be like below,
=aggr( sum({<TranDate={">=$(p(=date(min(CutOffDate))))<=$(Date(Max(TranDate))) "}>}Amount),UserId)
Hi Pham,
Please find the attached application.