How to get the latest global value within a filtered dimension

[mendoncart]

Hi, guys!

I'm having a problem, and already searched about and came across aggr, firsSortedValue and Concat, but it didn't work,

I'm looking the sales of a specific vendor (this vendor is selected with filters on dimension), and I want to know the latest purchase of each customer related to this vendor, even if is outside the filtered dimensions.

So, I'm using max() with set analysis to find the latest date, but I'm not being successful to bring the vendor related to this date when it is outside the dimensions, is there a way to use set analysis on a field?

Here is the formula to find the date, it works fine:

max({1<Utilização={'Venda de Mercadorias'},Tipo = {'Nfe'},CANCELED={'N'},BPLId={$(=Chr(39) & Replace(Branch, ',', Chr(39) & ',' & Chr(39)) & Chr(39))}>}DocDate)

This doesn't work (with/without Total, All):

FirstSortedValue(aggr(max({1<Utilização={'Venda de Mercadorias'},Tipo = {'Nfe'},CANCELED={'N'},BPLId={$(=Chr(39) & Replace(Branch, ',', Chr(39) & ',' & Chr(39)) & Chr(39))}>}DocDate), Total SlpName))

How can I use this formula to bring the vendor of the sale whether is inside or outside the selections?

[sunny_talwar]

May be this

FirstSortedValue({1<Utilização={'Venda de Mercadorias'},Tipo = {'Nfe'},CANCELED={'N'},BPLId={$(=Chr(39) & Replace(Branch, ',', Chr(39) & ',' & Chr(39)) & Chr(39))}>} SlpName, -DocDate)

[mendoncart]

The only value it returns is - , but with this at the table brings some other rows as well.

[dwforest]

A small tweak to what Sunny suggested:

FirstSortedValue({1<Utilização={'Venda de Mercadorias'},Tipo = {'Nfe'},CANCELED={'N'},BPLId={$(=Chr(39) & Replace(Branch, ',', Chr(39) & ',' & Chr(39)) & Chr(39))}>} SlpName, -Date#(DocDate))

[sunny_talwar · Accepted solution]

David I think if Max(DocDate) worked, then I don't think Date#() will be required here. May be try adding a distinct

FirstSortedValue(DISTINCT {1<Utilização={'Venda de Mercadorias'},Tipo = {'Nfe'},CANCELED={'N'},BPLId={$(=Chr(39) & Replace(Branch, ',', Chr(39) & ',' & Chr(39)) & Chr(39))}>} SlpName, -DocDate)

[mendoncart]

This worked like a charm Sunny, thank you!

Also thanks to David for helping me, this fixed my problem!

Could you explain to me how this formula works?

The FirstSortedValue is sorting the SlpName, with the set analysis, and ordering by decrescent dates, then taking the fist value? Can we use set analysis directly on a field like {1}SlpName?

Thank you guys again!

[sunny_talwar]

The FirstSortedValue is sorting the SlpName, with the set analysis, and ordering by decrescent dates, then taking the fist value?

Yup, you are right....

Can we use set analysis directly on a field like {1}SlpName?

You can do this

Only({1} SlpName)

but the above will only work, if you have one and only one value for SlpName. If you have more than one, then Only will not work. In that case, you can use Concat({1} SlpName) or MinString({1} SlpName) or MaxString({1} SlpName)

[mendoncart]

I've tried Concat, but it brings back the value several times over, so I'm using wildmatch to see if what I'm looking for is among the values concat brings back.

Thank you for all your patience and good will!

Have a wonderful week!

[sunny_talwar]

May be use

Concat(DISTINCT {1} SlpName)