Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Selecting 'last value' but based on a different column

Hello, I was hoping anyone could help me with this problem. See this 3x3 dataset in Query Editor:

When I want to create a table out of this in the report view, I would like to show only the row with the most recent date.

So, ideally, it would say:

 

However, when I select 'most recent date' under values in report view, it still shows all three rows, like this:

 

If I select to only show 'Last letter', it shows the c, not the a (probably because of alphabetical order):

 

How do I change this? I want the 'last letter' do display a, because that's the letter that belongs to the most recent date, not c.

 

Hopefully I've explained myself well! If not, please let me know.

 

Lex

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    You can add a column to your table to flag the latest record per id as follows:

    Flag = 
IF(
    T14[date] = 
    MAXX(
        FILTER(T14, T14[id] = EARLIER(T14[id])),
        T14[date]
    ),
    1,
    0
)

     

    Then, assign to the visual filter and set equal to 1

     

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn

     



4 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    You can use the TOPN option and assign one on the Filter Pane for Visual Filter

     

     

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Fowmy, thank you for your help. This seems to work for a single unique ID, but what if the table is to be expanded like this: 

       

      And you want to show the most recent date per id with the corresponding letter? It would show like this:

       

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

        Anonymous 

        You can add a column to your table to flag the latest record per id as follows:

        Flag = 
IF(
    T14[date] = 
    MAXX(
        FILTER(T14, T14[id] = EARLIER(T14[id])),
        T14[date]
    ),
    1,
    0
)

         

        Then, assign to the visual filter and set equal to 1

         

        ________________________

        If my answer was helpful, please click Accept it as the solution to help other members find it useful

        Click on the Thumbs-Up icon if you like this reply 🙂


        Website YouTube  LinkedIn