Using Variables to Pick "Closest Date"

AndrewMeierOptiv
AndrewMeierOptiv Member
in Variables

Hey! I wasn't sure if this went in Variables or Beast Mode questions. I have data that is currently structured:

Opp Name - Value - Date Updated

A - 100 - 3/15

A - 80 - 3/30

A - 120 - 4/5

B - 50 - 4/1

B - 75 - 4/7

The outcome I'm looking for is for a User to be able to input a Date (say 4/1) as a Variable and then have the dataset filter down to the closest Date Updated w/o going over for each Opp Name (3/30 for A, 4/1 for B). I've tried using a combo of a Variable and RANK() (along the lines of RANK() OVER(Partition BY 'Opp Name' Order BY DATEDIFF('Variable','Date Updated') and then filtering to Rank = 1 … but whenever i put this into a Bar Chart that tries to Aggregate the Value for some reason the Rank filter isn't taking and it's just Summing everything. I know it's risky/sketchy to use Window Functions as filters, and that might be causing the issue, so does anyone have any ideas of how to accomplish what i'm looking for?

TLDR version: I'm looking for a way get Total Value of all Opps on any given day as selected by the end user w/o bumping a calendar of every single day into my dataset because that would make the dataset waaaaay too large. Appreciate any help.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    Have you tried something like:

    CASE WHEN (`Variable`-`Date Updated`) = MIN(CASE WHEN `Date Updated` <= `Variable` THEN `Variable`-`Date Updated` END) OVER (PARTITION BY `Opp Name`) THEN 'Closest' ELSE 'Exclude' END

    And filtering on the beast mode = Closest?

    Regarding your beast mode do you have any aggregation that's being performed on the card?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • AndrewMeierOptiv
    AndrewMeierOptiv Member

    @GrantSmith I appreciate the quick response! This is a nice way to get around RANK() … however, I tried this and it still seems to be summing everything… which i guess to answer your question, yes, I am summing the Value field as the Y-Axis so there is an aggregation in my final chart. I'm not sure if that is what is throwing off the Beast Mode filter… i've attached a few screenshots of what i'm seeing. Appreciate your response to this!

    Beast Mode.png
    Filter SS.png

Categories