Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Asrif,
Please use the calculated dimension for date as
=aggr(date(max(TranDate),'DD/MM/YYYY'),CCTYPE)
possible to show the 1st desired result? is it possible?
Like this?
=FirstSortedValue(TranDate, -TranNo)
Well, I missed that you wanted the same in calculated dimension, may I know the purpose of the same?
I will check for the first result and update you.
Hi,
Please use this in your dimension for first output
=aggr(max(TranNo),CCTYPE)
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.
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.)
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)