Return a field value over Max(date)

[Anonymous]

Hi all

I have a Qlik table with all itens transactions records like this sample:

And I want a analysis with these Layout:

Where Last transaction is a calculated dimension with this expression:

=Aggr(Max({<CODE_EXT={'21','22'}>}  DATE_TRANSACTION), COD_ITEM, TYPE)

I need Location shows the correctly value for a combination of Item Code, Type and Transaction Date, but I couldn't find an expression that fits. Tried a simple =CODE_LOCATION, but without success.

If someone need the qvw code, I can put this later.

Tks for suggestions!

[sasiparupudi1]

May be try

Aggr(MaxString({<CODE_EXT={'21','22'}>}  Location), COD_ITEM, TYPE)

or may be simple maxstring(location)

[vishsaggi]

Or try like

= Firstsortedvalue(CODE_LOCATION, Aggr(Max({<CODE_EXT={'21','22'}>}  DATE_TRANSACTION), COD_ITEM, TYPE))

[Anonymous]

This don't worked.

With this expression, i get tha last Location for that Set Analisys, and I want the corresponding Location for that Max(date), Item Code and Type.

The correct values, are EXP on both.

[Anonymous]

This don't worked.

With this expression, the Firstsortedvalue shows CQ for Entrada Type.

The correct values, are EXP on both.

[vishsaggi]

Can you share a sample app you are trying this?

[Anonymous]

Post updated with a sample.

[vishsaggi]

Try this?

= FirstSortedValue(CODE_LOCATION, -Aggr(Max({<CODE_EXT={'21','22'} >} DATE_TRANSACTION), COD_ITEM, TYPE, COD_ITEM))

Sorry try this:

= FirstSortedValue(CODE_LOCATION, -Aggr(Max({<CODE_EXT={'21','22'} >} DATE_TRANSACTION), COD_ITEM, TYPE, CODE_EXT))

[sasiparupudi1]

Please try

SubField(Maxstring(Aggr(concat(Distinct If(Match(CODE_EXT,21,22),CODE_LOCATION &'|'& DATE_TRANSACTION),','),DATE_TRANSACTION)),'|',1)

[sunny_talwar]

Another option

=FirstSortedValue({<CODE_EXT={'21','22'}>} CODE_LOCATION, -Aggr(Max({<CODE_EXT={'21','22'} >} DATE_TRANSACTION), COD_ITEM, TYPE))