Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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

Re: Return a field value over Max(date)

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

Re: Return a field value over Max(date)

May be try

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

or may be simple maxstring(location)

Highlighted
Champion III
Champion III

Re: Return a field value over Max(date)

Or try like

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

Highlighted
Contributor III
Contributor III

Re: Return a field value over Max(date)

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

Re: Return a field value over Max(date)

This don't worked.

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

The correct values, are EXP on both.

Screenshot_3.png

Highlighted
Champion III
Champion III

Re: Return a field value over Max(date)

Can you share a sample app you are trying this?

Highlighted
Contributor III
Contributor III

Re: Return a field value over Max(date)

Post updated with a sample.

Highlighted
Champion III
Champion III

Re: Return a field value over Max(date)

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

Re: Return a field value over Max(date)

Please try

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

Highlighted

Re: Return a field value over Max(date)

Another option

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