Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
kenphamvn
Contributor III

Calculation between two

Dear All

i want to calculation sum of values from two date columns

Here is my sample data

UserIDCutOffDateTransDateAmount
User013/12/20183/10/201810
User013/12/20183/11/201810
User013/12/20183/12/201810
User013/12/20183/13/201810
User013/12/20183/14/201810
User013/12/20183/15/201810
User013/12/20183/16/201810
User023/15/20183/14/201810
User023/15/20183/15/201810
User023/15/20183/16/201810

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

UserIDSum AmountDescription
User0150Sum of Amount from Cutoffdate of User01 (3/12/2018) to Max(TransDate) ('3/16/2018')
User0220Sum 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

UserIDSum AmountDescription
User0130Sum of Amount from Cutoffdate of User01 (3/12/2018) to Max(TransDate) ('3/14/2018')
User020Because Cutoffdate > Max(TransDate)


but i am not sure how to write expression correctly.

Any help would be appreciated.

Thanks in advanced

An Pham

3 Replies

Re: Calculation between two

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)

arunaerra
Contributor

Re: Calculation between two

Hi,

may be like below,

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

sumanta12
Contributor II

Re: Calculation between two

Hi Pham,

Please find the attached application.