1 Reply Latest reply on Oct 13, 2016 4:38 PM by Shinichiro Murakami

    Switch between Monthly and YTD on a dashboard when YTD is sum of Monthly values

    Lauren Drake

      Hello! I am having a devil of a time figuring this out even though I feel like it must be simple. I've attached a greatly simplified version of my workbook that will hopefully illustrate my problem.

       

      I have a large number of measures that get multiplied out by a few variables to calculate monthly dollar savings. Because of the nature of the calcs and the raw data I have, the YTD value cannot be calculated by doing the same calculation that is done on the monthly values- the YTD $ calculation has to be the sum of the monthly values.

      (To illustrate- I'm doing things like multiplying the number of worked hours per customer by the avg. hourly rate. The multiple data systems we have prevent me from getting an accurate YTD avg. hourly rate- I only have monthly hourly rates, not YTD weighted hourly rates.) I can only seem to accomplish this via window_sum, as the totals available do not give me the correct value. Perhaps there is a better way?

       

      I need to create a dashboard that will show the Monthly or YTD value for the measure, and the associated dollars for those measures. Right now, I have my YTD dollars in a separate graph with the months hidden since I can't filter them out, as the window_sum would not be accurate.

       

      I tried doing sheet switching based on parameters with Display based on sheet names, but my coworker and I couldn't figure out how to do it without needing multiple sheets to be named the same thing, as I would need the parameter to display and hide multiple sheets.

       

      Imagine that there are many, many more measures across multiple facilities, all with associated monthly and YTD dollar calcs.

       

      Thank you for any assistance you can provide, even if it is a link to a thread I should have been able to find myself.

       

      Lauren

        • 1. Re: Switch between Monthly and YTD on a dashboard when YTD is sum of Monthly values
          Shinichiro Murakami

          I don't know I could fix the issue, but here is my approach.

          Still use parameter.

           

           

           

          [Time Period SM]

          if [Time Period]=[Parameter 1] then [Time Period] end

           

           

          [Sheet_Display]

          [Parameter 1]="YTD"

           

           

          Put Sheet Display to the filter card.

          And on Monthly Sheet, Only select "False"

          On the YTD Sheet, Only select "True"

           

          With this, according to the parameter, one of each can be invisible.

           

           

           

           

           

          On dashboard, Put container of horizontal.

           

          Then put monthly into this container.

           

          When you pick parameter YTD, the view is gone.

           

          with this screen, put YTD sheet on top of monthly sheet in same container.

           

           

           

          I don't know which sheet should be swapped by parameter, but the trick is the same.

           

           

           

          I add the title box because I need to hide the sheet title from invisible view.

           

          [title]

          if [Sheet_Display] then "$YTD" else "$ Monthly" end

           

           

           

          Hope these help.

           

           

          Thanks,

          Shin