Skip to main content

Referencing this topic: Relative Date and Custom Date filter

 

1) I want to take it a step further and hide the relative dates (Start and End date Parameters). 

OR

2) Can I populate the start and end date parameters based on my selection?

 

For example:

Past Month would display 6/1/18 in the start date and 6/30/18 in the end date.

YTD would display 1/1/18 in the start date and 7/12/18 in the end date, etc

 

If that is not possible then I just want to be able to hide the start and end date parameters unless I choose CUSTOM.

 

--------------------------------------------------------------------------------------------

Here is the case statement for the drop-down Date Filter:

CASE [Select Range]

WHEN "Most Recent Date" THEN

    //Sunday

    IIF (DATEPART('weekday',TODAY()) = 1,(IF DATEADD('day',-3,TODAY()) = [Date] THEN "Keep" Else "Drop" END),

    //Monday

    IIF (DATEPART('weekday',TODAY()) = 2,(IF DATEADD('day',-4,TODAY()) = [Date] THEN "Keep" Else "Drop" END),

    //All other days

    IIF (DATEADD('day',-2,TODAY()) = [Date],"Keep","Drop")))

WHEN "Past Week" THEN

    IF DATEPART('weekday',TODAY()) = 2 THEN

    (IF (DATEPART('week',[Date]) = DATEPART('week',TODAY())-1 OR (DATEPART('week',TODAY()) <= 1 AND DATEPART('week',[Date]) = 1)) AND DATEPART('weekday',[Date]) < 6 AND DATEPART('weekday',[Date]) > 1 AND [Date] > DATEADD('day',-365,TODAY()) THEN "Keep" END)

    ELSE

    IF (DATEPART('week',[Date]) = DATEPART('week',TODAY())-1 OR (DATEPART('week',TODAY()) <= 1 AND DATEPART('week',[Date]) = 1)) AND [Date] > DATEADD('day',-365,TODAY()) THEN "Keep" ELSE "Drop" END END

WHEN "Current Month" THEN

    IF DATEPART('month',TODAY()) = DATEPART('month',[Date]) and [Date] > DATEADD('day',-180,TODAY()) and [Date] <= {[Last Transmited Data]} THEN "Keep" Else "Drop" END

WHEN "Past Month" THEN

    IF (DATEPART('month',TODAY())-1 = DATEPART('month',[Date]) OR (DATEPART('month',TODAY()) = 1 AND DATEPART('month',[Date]) = 12)) and [Date] > DATEADD('day',-180,TODAY()) THEN "Keep" Else "Drop" END

WHEN "YTD" THEN

    IF DATEPART('year',[Date]) = DATEPART('year',TODAY()) AND [Date] <= {[Last Transmited Data]} THEN "Keep" Else "Drop" END

WHEN "Custom" THEN

    IIF([Date]>=[Start Date] AND [Date]<=[End Date],"Keep","Drop")

END

2 answers
  1. Jul 24, 2018, 9:29 PM

    Hi Angela  see the thread Re: Pop up a sheet based on a specific filter value?

     

    Joe Opplet wrote on how use a pop out sheet to hide or show a sheet or floating portion of a sheet -

    They process changed with 2018-1 but the link will take you through how it is done

     

    But a Word of caution - parameters are user set static values that are global - they are constant across all worksheets and dashboard until changed by the user

    you can apply them or not apply them on different sheets based on you  use them in calculations or filters -

     

    Jim

    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

0/9000