topic Re: Display Max transaction or max tran date in calculated dimension in QlikView

Display Max transaction or max tran date in calculated dimension

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

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?

Re: Display Max transaction or max tran date in calculated dimension

tharanikannan — Thu, 01 Nov 2018 03:25:43 GMT

Hi Asrif,

Please use the calculated dimension for date as

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

Re: Display Max transaction or max tran date in calculated dimension

Anonymous — Thu, 01 Nov 2018 03:57:46 GMT

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

Re: Display Max transaction or max tran date in calculated dimension

tresB — Thu, 01 Nov 2018 04:34:45 GMT

Like this?

=FirstSortedValue(TranDate, -TranNo)

Re: Display Max transaction or max tran date in calculated dimension

tresB — Thu, 01 Nov 2018 04:40:57 GMT

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

Re: Display Max transaction or max tran date in calculated dimension

tharanikannan — Thu, 01 Nov 2018 04:53:08 GMT

I will check for the first result and update you.

Re: Display Max transaction or max tran date in calculated dimension

tharanikannan — Thu, 01 Nov 2018 04:58:57 GMT

Hi,

Please use this in your dimension for first output

=aggr(max(TranNo),CCTYPE)

Re: Display Max transaction or max tran date in calculated dimension

Anonymous — Thu, 01 Nov 2018 05:38:59 GMT

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.

Re: Display Max transaction or max tran date in calculated dimension

Anonymous — Thu, 01 Nov 2018 05:46:10 GMT

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.)

Re: Display Max transaction or max tran date in calculated dimension

tresB — Thu, 01 Nov 2018 05:58:23 GMT

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)

Re: Display Max transaction or max tran date in calculated dimension

Anonymous — Fri, 02 Nov 2018 01:57:19 GMT

Say if the table for the transaction is Many to Many? the firstsortedvalue cant work?

Later i will try to replicate using a few samples and see if this can be done.

Re: Display Max transaction or max tran date in calculated dimension

Anonymous — Mon, 05 Nov 2018 03:16:11 GMT

hi, i did load a sample of the data in new to qliksense,

can u access it and see how the firstsortedvalue can work in that many to one or many to many data set???