Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Return a field value over Max(date)

Hi all

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

Screenshot_2.png

And I want a analysis with these Layout:

Screenshot_1.png

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!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

I did not go through the entire discussion. Therefore, I might be wrong missing some. I was wondering if a simpler expression could work.

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

Since, you are using the set analysis in firstsortedvalue() already, the DATE_TRANSACTION would consider the filter before sorting them, hence an additional aggr() and max() might not be required (the aggregation at dimensions are already happening since they are there in the chart as dimensions).

And yes, the third calculated dimension should probably be used as an expression (Max({<CODE_EXT={'21','22'}>}  date(DATE_TRANSACTION))) that could again help do away with aggr().

View solution in original post

19 Replies
Highlighted
Master III
Master III

May be try

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

or may be simple maxstring(location)

Highlighted
Champion III
Champion III

Or try like

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

Highlighted
Contributor III
Contributor III

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.

Screenshot_4.png

Highlighted
Contributor III
Contributor III

This don't worked.

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

The correct values, are EXP on both.

Screenshot_3.png

Champion III
Champion III

Can you share a sample app you are trying this?

Highlighted
Contributor III
Contributor III

Post updated with a sample.

Highlighted
Champion III
Champion III

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

Highlighted
Master III
Master III

Please try

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

Highlighted

Another option

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