Solved

Selecting the 2nd Most Recent value for the Transaction list

  • 7 November 2023
  • 7 replies
  • 199 views

Userlevel 3
Badge +8

When I want to get the most recent date of the sale of a particular product, I can use BY FIRSTNONBLANK aggregator to get the value from the Transaction list of product sales data into the metric.

But, how do I select the 2nd most recent date?

For instance, I have the following sales table:

Product         Qty.        Date
Product A.      30.        5/12/2023
Product A.      40         3/26/2023
Product A.      15.        1/11/2023

I would like the metric, which I’m creating, select the 2nd row’s date, i.e.:

Product A.   3/26/2023

icon

Best answer by Benoit 8 November 2023, 10:45

View original

7 replies

Userlevel 3
Badge +8

And, it should select the 2nd most recent date regardless of the row location.

i.e. if the data table like this:

Product         Qty.        Date
Product A.      30.        5/12/2023
Product A.      40         1/11/2023
Product A.      15.        3/26/2023

It still needs to select: Product A.   3/26/2023

Userlevel 3
Badge +6

Hello @Dastan ,

 

I hope someone else in the community has a better answer that the one coming up as it is a bit convoluted, but it should lead you to what you are after.

 

  1. You are going to create a RANK - information here - in the Dates to evaluate when is your 2nd most recent event.
    • The formula could be something akin of the following:
    • rank(Transaction list name.'Date',Transaction list name.'Product Dimension',asc,sequential)
  2. Create a new dimension with two items manually input. Name them 1 and 2
  3. Create a new property in the transactional list where you are going to use the ITEM function - information here - to map your Rank with the dimension you just created.
    • The formula could be something akin of the following:
    • item(text(Transactional list name.Rank), '1-2'.Name)
  4. Build your target metric dimensioned by Product and formatted as Date.
  5. Apply the following formula:
    • 'Transactional list name'.'Date'[by first: 'Transactional list name'.'Product', 'Transactional list name'.'1-2'][filter: '1-2'='1-2'."2"][remove firstnonblank: '1-2']

 

That should resolve in the dates that you are after. I hope it makes sense and, as I said, that someone provides an easier answer.

 

Cheers!

Userlevel 6
Badge +12

Hi,

I might have something as well.

First, rank your dates in your transaction list using the Rank function

And then use the List.rank = 2  condition to isolate your rows:

 

This metric renders the quantities for the second most recent dates.

 

Hope this helps.

Best,

 

Userlevel 3
Badge +8

Thank you guys. It worked. But now, I have follow-up question:

How can I use ranking numbers to bring the subsequent row’s date value into the column next to the ranked column? Here:

 

Userlevel 3
Badge +8

So, I need to look previous ranked row and bring it’s date value into the current ranked row

Userlevel 3
Badge +8

Nevermind, actually I was able to do it with the use of Ranking Dimension, creating metrics, and using SELECT: Ranks-1

Userlevel 3
Badge +6

Hey @Dastan ,

 

Happy to read that it worked and you managed to resolve subsequent issues, good job!

 

Kind regards,

Bruno

Reply