Display Max transaction or max tran date in calculated dimension

[Anonymous]

Hi Guys,

     Assuming i have this table.

CCTYPE	TranNo	amount	TranDate
K902329AB	2	1000	01/02/2010
K902329AB	4	1000	02/02/2010
K902329AB	7	1000	05/02/2010
K902330AB	1	1000	11/02/2010
K902330AB	2	1000	12/02/2010
K902330AB	5	1000	13/02/2010
K902331AC	1	1000	22/02/2010
K902331AC	3	1000	23/02/2010
K902331AC	4	1000	24/02/2010
K902331AC	6	1000	25/02/2010

I'm trying to achieve these result in calculated dimension as per below in pivot table or straight table.

1st desired result

CCTYPE	Max(tranno)	=SUM(Amount)
K902329AB	7	3000
K902330AB	5	3000
K902331AC	6	4000

2nd Desired result

CCTYPE	date of Max(tranno)	=SUM(Amount)
K902329AB	05/02/2010	3000
K902330AB	13/02/2010	3000
K902331AC	25/02/2010	4000

Any idea how?

[tharanikannan]

Hi Asrif,

Please use the calculated dimension for date as

=aggr(date(max(TranDate),'DD/MM/YYYY'),CCTYPE)

[Anonymous]

possible to show the 1st desired result? is it possible?

[tresesco · Accepted solution]

Like this?

=FirstSortedValue(TranDate, -TranNo)

[tresesco]

Well, I missed that you wanted the same in calculated dimension, may I know the purpose of the same?

[tharanikannan]

I will check for the first result and update you.

[tharanikannan]

Hi,

Please use this in your dimension for first output

=aggr(max(TranNo),CCTYPE)

[Anonymous]

the first was just to get the max from the same column,

the second was actually to get the value from the max of other column.

From these scenario , I understand that both can work as we are using max of transaction date or max of transaction number. but your solution with firstsortedvalue seems the one i'm looking for.

Thanks Tresesco B and thanks to Tharani as well.

[Anonymous]

This was a simple table, the actual table i have was more complicated and joined with several keys with another table. I wonder why the FirstSortedvalue or just using Max(tranno) didnt work. Those this only work when all the information is in one table and not connected to other table? ( e.g. the tranno  information is in another table and joined with CCTYPE as key. and the trandate is in another table.)

[tresesco]

No it's not necessary that they be in the same table. However, the association and mapping type, i.e- one-to-one/one-to-many/many-to-many matters. Without knowing about your data model it would be hard to troubleshoot. However, you could give a try with DISTINCT keyword like:

=FirstSortedValue(DISTINCT TranDate, -TranNo)