0

Year Filter

I have a dashboard with a bunch of klips that pull data based on dates. Most often I'm pulling data for the current year and comparing it to the previous year. Often I'm doing it for the current year to date, compared to last year to date. But, I just realized, that as soon as it ticks over to 2021, I'll have no way of going back and looking at 2020's data. So I thought I'd do a filter with years in a dropdown, and use that instead of TODAY() but I'm struggling to make it work. Here's an existing formula. You can see I'm already using filters for Location and SalesRep. But if I try to swap out TODAY() with $YearFilter, for example, it doesn't work (I knew it couldn't be that simple.) Help. I only have a couple of weeks before I lose all this valuable data (I mean, I know it'll still be there, but I won't be able to see it.)

SELECT(
Zip Code;,
AND(
BETWEEN(
DATE(Won/Lost Date;, "M/d/yyyy"),
DATE_STARTOF(TODAY(), "1"),
TODAY()
),
Estimate Number; != BLANK(),
Total; != "0",
Status; != "Cancelled",
OR(Company Name; = $LocationFilter, $LocationFilter = "_all_"),
OR(Job Sales Rep.; = $SalesRepFilter, $SalesRepFilter = "_all_")
)
)

1 comment

  • 0
    Avatar
    Dale Pease

    I figured out how to do it with two input control filters (Start Date and End Date)

    BETWEEN(
    DATE(Won/Lost Date;, "M/d/yyyy"),
    DATE_STARTOF($DateStartFilter, "1"),
    $DateEndFilter
    )

    So, I think for now I've got my answer.

Please sign in to leave a comment.