Unable to take max date

[#1]

Hi,

I am trying to calculate the total by customer group based on the max trxdate of the customers.

For ex, consider the below table

Cust, trxdate, amt, Group

    1, 01-01-2017, 190, A

    1, 02-01-2017, 21, A

    1, 03-01-2017, 23, A

    2, 04-01-2017, 121, A

    2, 05-01-2017, 232, A

    3, 06-01-2017, 123, B

    3, 07-01-2017, 121, B

my expected output will be

Group      value

A             255(23+232)     (03-01-2017 & 05-01-2017)

B             121

I tried the logic by writing set analysis,

aggr(sum({<trxdate_num={"=aggr(max(trxdate_num),cust)"}>}amt),Group)

but, the above expression returns the min date values,like for Group A, it returns 311(190+121).

can anyone help me on this issue.

Thanks in advance.

[#2]

Thanks for you prompt response, but its not working as expected.

Any other solutions...

Thanks in advance.

[sunny_talwar]

May be this

Sum(Aggr(FirstSortedValue(amt, -trxdate), Cust, Group))

or

Sum(Aggr(If(trxdate = Max(Total <Cust> trxdate), amt), Cust, Group))

[#4]

Hi,

First expression is working fine. Is there any other way to do this instead of firstsortedorder method.

like any set analysis way...

Thanks in advance.

[sunny_talwar]

You can use set analysis if you are willing to make flags in the script.... bu then these flags will be static and won't change based on selections.

The problem with set analysis is that it is evaluated once per chart. If you are looking to get different amt for each Group which has a different max trx_date. Set analysis can't get you this information... You will either need FirstSortedValue or Aggr() function to do this....

[#6]

Thanks Talwar.

If you dont mind, May i know what is wrong in the below expression, Why its return min date instead of max date.

aggr(sum({<trxdate_num={"=aggr(max(trxdate_num),cust)"}>}amt),Group)

Thanks in advance.

[sunny_talwar]

You have once again used set analysis