Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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().
May be try
Aggr(MaxString({<CODE_EXT={'21','22'}>} Location), COD_ITEM, TYPE)
or may be simple maxstring(location)
Or try like
= Firstsortedvalue(CODE_LOCATION, Aggr(Max({<CODE_EXT={'21','22'}>} DATE_TRANSACTION), COD_ITEM, TYPE))
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.
This don't worked.
With this expression, the Firstsortedvalue shows CQ for Entrada Type.
The correct values, are EXP on both.
Can you share a sample app you are trying this?
Post updated with a sample.
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))
Please try
SubField(Maxstring(Aggr(concat(Distinct If(Match(CODE_EXT,21,22),CODE_LOCATION &'|'& DATE_TRANSACTION),','),DATE_TRANSACTION)),'|',1)
Another option
=FirstSortedValue({<CODE_EXT={'21','22'}>} CODE_LOCATION, -Aggr(Max({<CODE_EXT={'21','22'} >} DATE_TRANSACTION), COD_ITEM, TYPE))