Forum Discussion

JordanBeatty1's avatar
JordanBeatty1
Qrew Captain
8 years ago

Use initial filters to display current month, and current month last year

I'm trying to make a report that use initial filters to display the current month and the current month last year. So for example May 2016 and May 2017, for a comparison. Month and Year are stored in the same field. Filtering for the current month is easy, though is it possible to filter for the current month last year if Month and Year are stored in the same field?
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    you might create a formula text field to filter on.

    For Month and Year combo:

    case(month([date field]),
    1,"January "&year([date field]),
    2,"February "&year([date field]),
    3,"March "&year([date field]),
    4,"April "&year([date field]),
    5,"May "&year([date field]),
    6,"June "&year([date field]),
    7,"July "&year([date field]),
    8,"August "&year([date field]),
    9,"September "&year([date field]),
    10,"October "&year([date field]),
    11,"November "&year([date field]),
    12,"December "&year([date field])
    )

    To separate out month and year,

    Create a formula text field called 'Year'. Formula is: year([date field])

    Create a formula text field called 'Month'. Formula is:

    case(month([date field]),
    1,"January",
    2,"February",
    3,"March",
    4,"April",
    5,"May",
    6,"June",
    7,"July",
    8,"August",
    9,"September",
    10,"October",
    11,"November",
    12,"December"
    )

    Let me know if that works for you. =]
  • JordanBeatty1's avatar
    JordanBeatty1
    Qrew Captain
    8 years ago
    I created all these fields, though I am unsure how to setup the filtering as they are text fields.

    Currently I have:

    [Date Referred] is during the current month

    Though I am unsure how to filter these new field to get the results of the current month last year,