Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
mendoncart
Contributor III
Contributor III

How to get the latest global value within a filtered dimension

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?

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

8 Replies
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
Contributor III
Contributor III
Author

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

dwforest
Specialist II
Specialist II

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

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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)