Extract the latest value based on the latest date with respect to combination of fields

Question & Answer
, , ,
1

Hi All. Really need your help on this one. Below is the screenshot of the dataset that I have along with the expected outcome I am looking for:
image

As shown, I have to put only brand, country & SKU within the GroupBy (Field Well) and am planning to calculate the following:

  1. First, the Max Modified Date based on (Brand, Country, SKU) combination which would give me max_modified_date field.
  2. Second, the latest Unit Cost based on (Brand, Country, SKU, max_modified_date) which would give me latest_unit_cost field.

P.S. I can’t include Modified_date within the Field Well ‘Group by’ section.

Any quick help would be greatly appreciated.

2

Hey @Thomas @ErikG … Guys really appreciate your help on this…

3

Hi @sibasish.padhy,

You can calculate a rank based on the date and then filter your visual by rank = 1.

Rank:

rank([Modified_date DESC], [Brand, Country, SKU], PRE_AGG)

Then add all the fields to a table visual and filter it by Rank = 1.

4

@sibasish.padhy maybe lastValue will work, too, in case you need a field.