2 Replies Latest reply on Jul 10, 2018 7:37 AM by Ben Olson

    Trying to Show One value or the other

    Ben Olson

      I have a report that shows a 'Month to Date' aggregation of sales dollars. I have set the report to automatically change based on the current month. The hang up is my company uses a 4-5-4 fiscal calendar and the current month does not always equal the fiscal month.For example, when the report runs on June 4, 2018, the current month is June, but I want my report to show May's numbers. I created the following formula:

       

      IF [Current Month Match Fiscal Month, Yes or No] = "Yes"

      AND [Fiscal Year No] = [Current Year]

      AND  [Fiscal Week No] <= [Previous Week]

      THEN [Net Dollars]

      ELSEIF   [Current Month Match Fiscal Month, Yes or No] = "No"

      AND [Fiscal Year No] = [Current Year]

      AND [Month by Selected Fiscal Week] = [Previous Month]

      AND [Fiscal Week No] <= [Previous Week]

      THEN [Net Dollars]

      END

       

      So the output shows the following:

       

       

      Is there a formula that would show either the "Yes" value or the "No" value, not both.

       

      Thanks

      Ben