Dynamic set analisys by each row

[Anonymous]

Hi Qlik experts,

I have a situation where I have a list of sellers which I desire to show the store where its seller made his/her last sell

I wanto show:

SalesmanName         sum(SalesValue)       Store

I tried

only(aggr(only({<Date= {"$(=only(aggr(max(Date),SalesmanName)))"}>} Store),SalesmanName))

but that returs null when I select more than one Date. It works when I select salesmen with the same last sales date, but that is not valid for the whole set.

Thanks in advance.

RS

[sunny_talwar]

May be try this

FirstSortedValue(Store, -Date)

[Anonymous]

Hi Sunny,

thanks for replying but it returned null for all cases in my set, even for one SalesmanName.

[sunny_talwar]

Would you be able to share a sample to see what you have?

[Anonymous]

follow attached

RS

[sunny_talwar · Accepted solution]

Can there be more than one store with the max date? If not, then try this

FirstSortedValue(DISTINCT Store,-Date)

for more than one store, you can try this

Concat(DISTINCT Aggr(If(Date = Max(TOTAL <SalesmanName> Date), Store), Date, SalesmanName))

[Anonymous]

It worked perfect with the distinct (the store is a date parameter)

thanks a lot Sunny

RS