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.

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

you are an absoltule star, my only problem now is that as the date table is across multiple year it idnetifies the correct fiscal quarter but identify all QTR4's in all year ie 2014,2015,2016,2017,

 

I feel i am taking liabilities abit now so will try and do some more research to work out if i can work this final problem out myself, as dont want to take advantage of your good nature,!!! thank you so much for your help

 

Tim

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,