8 Replies Latest reply: Jun 28, 2017 9:05 AM by Sunny Talwar RSS

    How to get the latest global value within a filtered dimension

    Ruytter Mendonça

      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?