Forum Discussion

tmears's avatar
tmears
Icon for Helper III rankHelper III
9 years ago
Solved

Display this week & this month's data filter/Flag

Hi   I would like to put a marker in my Calander table which will change dynmaicly according to the present date.  I would like a marker for today, this week, this month, this quater and year.    ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    Generally this is done with an "IsToday" kind of column. So, assuming that you have a Calendar/Date table like:

     

    Date

    1/1/2017

     

    You could build a column like:

     

    IsToday = IF(DAY([Date]) = DAY(TODAY()) && MONTH([Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()),1,0)

    There are variations on this theme depending on your data format but this is the general gist of things. Your other flags would be similar formulas.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago
IsCurrentFiscalQuarter = 

VAR fiscal_month_today = If( Month(TODAY()) >= 4  , Month(TODAY()) - 3,Month(TODAY()) + 9 )

VAR fiscal_quarter_number_today = ROUNDUP(fiscal_month_today/3,0)

VAR fiscal_quarter_display_today = "FQ" & FORMAT(fiscal_quarter_number_today,"0")

RETURN IF(fiscal_quarter_display_today = [FiscalQuarterDisplay] && YEAR([Date]) = YEAR(TODAY()),1,0)

Just a bit of a combination of the previous few formulas. :)

tmears's avatar
tmears
Icon for Helper III rankHelper III
9 years ago

you are a legend!!  thank you so much,