topic Re: Values only calculate the most recent date in QlikView

Values only calculate the most recent date

neapicture — Wed, 25 Nov 2020 16:16:04 GMT

Hello

I would like to calculate a sum (Amount) and number per reminder level (Level) by customer (Account).
If the account occurs multiple times, only the amount of the most recent date of the record should be calculated.
The dimension should be the level.

I can not get any further here ...

Re: Values only calculate the most recent date

sunny_talwar — Thu, 02 Nov 2017 11:54:53 GMT

May be this

=Sum(Aggr(If(Date = Max(TOTAL <Account> Date), amount), Date, Level, Account))

Re: Values only calculate the most recent date

neapicture — Thu, 02 Nov 2017 12:30:03 GMT

A heartfelt thank you . It works. I have still one question. How does the formula change if the canceled values are not to be calculated? I forgot that. Sorry

Re: Values only calculate the most recent date

sunny_talwar — Thu, 02 Nov 2017 12:35:16 GMT

Script

Table:

LOAD *,

If(Len(Trim(Storno)) > 0, 1, 0) as ExcludeFlag;

LOAD * INLINE [

Date, Account, amount, Level, Storno

9/13/2017, 20000000054, 86, 2

9/15/2017, 20000000054, 86, 2,

9/25/2017, 20000000647, 85, 1,

9/28/2017, 20000000624, 43, 1,

10/2/2017, 20000000054, 86, 2,

10/4/2017, 20000000731, 215, 2,

10/4/2017, 20000000624, 43, 1, x

10/5/2017, 20000000054, 86, 2

10/5/2017, 20000000054, 86, 2,

10/6/2017, 20000000054, 86, 2,

10/9/2017, 20000000054, 86, 2,

10/9/2017, 20000000054, 86, 2

];

Expression

=Sum({<ExcludeFlag = {0}>}Aggr(If(Only({<ExcludeFlag = {0}>}Date) = Max(TOTAL <Account> Date), Only({<ExcludeFlag = {0}>}amount)), Date, Level, Account))

topic Re: Values ​​only calculate the most recent date in QlikView

Values ​​only calculate the most recent date

Re: Values ​​only calculate the most recent date

Re: Values ​​only calculate the most recent date

Re: Values ​​only calculate the most recent date

topic Re: Values only calculate the most recent date in QlikView

Values only calculate the most recent date

Re: Values only calculate the most recent date

Re: Values only calculate the most recent date

Re: Values only calculate the most recent date