Showing the highest value

[csmqlik01]

Hi,

I have follewing table ,  agecountdays is a calculated field based on batch and posting date with AGGR 

=[Posting date]- Aggr(nodistinct Min([Posting date]), Batch)

But now I want only the line with the highest days , and on C1 - End SoS & SiT , so can this within the above syntax 

Batch	[Posting date]	AgeCountDays	Quantity tons	Key Column-a
21848A1325	1/11/2017	0	0,620	A1 - Start SoS & SiT
21848A1325	30/11/2017	29	-0,620	C1 - End SoS & SiT
21848A1325	1/12/2017	30	0,620	A1 - Start SoS & SiT
21848A1325	31/12/2017	60	-0,620	C1 - End SoS & SiT
21848A1325	1/01/2018	61	0,620	A1 - Start SoS & SiT
21848A1325	31/01/2018	91	-0,620	C1 - End SoS & SiT
21848A1325	1/02/2018	92	0,620	A1 - Start SoS & SiT
21848A1325	28/02/2018	119	-0,620	C1 - End SoS & SiT

Regards,

[vishsaggi]

Not sure if this is good way of doing it but may be you can try like below:

LOAD *,
AutoNumber(PostingDt,Year(PostingDt)&Month(PostingDt)) AS SeqID;
LOAD Batch,
Date(Num(Floor(Date#("[Posting date]", 'DD/MM/YYYY'))),'DD/MM/YYYY') AS PostingDt,
AgeCountDays,
[Quantity tons],
[Key Column-a]
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Showing-the-highest-value/m-p/1552521#M128865]
(html, utf8, embedded labels, table is @1);

Then add straight table with Dimensions as
=IF(SeqID = 2,Batch) ---> For this calculated dimension check suppress when value is null.
PostingDate
Key Column-a

Expression as :
=Interval(PostingDt - Aggr(NODISTINCT Min(PostingDt), Batch), 'D')

Then you should see only Max Ages. Try and let me know if it works.

[csmqlik01]

Thanks for the suggestion,

But I was more thinking of a solution in an expression, all the dimensions are loaded from a SQL server,  the example is just a small part  of the model. And the field AgeCountDays is an calculated field.

[vishsaggi]

The best way to handle this is through script i feel, it will be resource heavy to handle this in UI with calculated aggr dimensions or expressions. Have to try in UI how to handle this meanwhile if you think you can do a resident load or preceding load to your SQL script adding those date dimensions, it will perform well. Will keep you posted about UI solution once i work on it.

[csmqlik01 · Accepted solution]

Hi,

Thanks for your effort and search 

But I created a second table where i used the AGGR function with distinct and then it shows only one line with the batch

Maybe this is the best, i have now a detail table and an overview table.

Best regards,

[vishsaggi]

Not sure i understood your table. If it suffice your requirement that is great. You can close the thread accordingly.