2 Replies Latest reply on Dec 6, 2016 6:31 AM by Josh Delekta

    Grouping Expected Data Not Yet In Data Source

    Josh Delekta

      Hello,

       

      Please see attached. I've done some research and can't seem to figure out how to accomplish the following:

       

      - Define this year and every year after in the dashboard as October through October. This can be accomplished by groups but how do you group dates that aren't in the data source yet? The data source will be updated on a weekly basis and I can't populate fake dates in that source.

      - Program the dashboard to switch over to the new year every October. In other words, the area chart and worksheet would build up or down week after week but once October 2016 data is provided the dashboard switches over to the new year and the area chart and the worksheet only show the new "year's" data in the view and begin building upward or downward again.

      - Allow the user to filter on previous years as defined as October through October. I've added "Year of Run Date" in the dashboard as example of where I would think user would filter to toggle between different years.

       

      I'm sure there are a couple of ways to accomplish this but I'm not sure what would be the most effective for "set it and forget it" as described above. The idea is that users would view their dashboard to see whether or not items that are billed or unbilled have been trending upward or down over the course of the year. The year is defined as October through October and they would be able to go back and quickly look at previous years when necessary.

       

      Thank you! I appreciate your thoughts on this example.

        • 1. Re: Grouping Expected Data Not Yet In Data Source
          Jeremy Harris

          Hi Josh,

           

          I attached a workbook that I believe does everything you are looking for. I replaced the "Year of Run Date" filter with a calculated dynamic "Fiscal Year" filter. Responses to your individual questions below.

           

          - Define this year and every year after in the dashboard as October through October. This can be accomplished by groups but how do you group dates that aren't in the data source yet? The data source will be updated on a weekly basis and I can't populate fake dates in that source.

          Created via calculated field called "Run Date FY"

          - Program the dashboard to switch over to the new year every October. In other words, the area chart and worksheet would build up or down week after week but once October 2016 data is provided the dashboard switches over to the new year and the area chart and the worksheet only show the new "year's" data in the view and begin building upward or downward again.

          Created Fiscal Year filter that finds the most recent run date in the data set, calculates its fiscal year, and sets the "Most Recent Year" value in the Fiscal Year filter to that value.

          - Allow the user to filter on previous years as defined as October through October. I've added "Year of Run Date" in the dashboard as example of where I would think user would filter to toggle between different years.

          As a new fiscal year trickles in, the "Most Recent Year" option will increment to 2018 and 2017 will appear as the next option in the drop down. I also added the Fiscal Year filter to context and made the Week of Run Date filter context sensitive so that list of weeks will update based on the selected Fiscal Year.

          1 of 1 people found this helpful
          • 2. Re: Grouping Expected Data Not Yet In Data Source
            Josh Delekta

            Thank you. This makes complete sense that multiple filters would need to be used. I appreciate your help.