8 Replies Latest reply on Aug 19, 2013 9:07 AM by Antonio Willybiro

    Full year view

    Antonio Willybiro

      Hi there - I have data structure periodically, by month.

      I need to have a table that show the year to date value say "jan, feb, march, april, may, june" and I need a switch so that I can show the full year view ie January to December.

      the YTD value are on the column YTD whereas the full year data are on a column QRF (quarterly reforecast).

       

      sample structure

       

           

       

       

       

       

       

       

                                              

      reporting_periodaccount_numberaccount_namecurrencyytd_reporting_period_valueqrf_valueproforma_value
      2013.07AC=xxxxREVENUEUSD700006900065000
      2013.08AC=yyyyyREVENUEUSD800008200078000

       

       

      Thanks in advance

      Antonio

       

       

          

        

        

           

           

           

       

           

           

           

        

       

      reporting_periodaccount_numberaccount_namecurrencyytd_reporting_period_valueqrf_valueproforma_value
      2013.07AC=10000TOTAL BILLINGS AND FEESUSD044965.1247255.81
      2013.07AC=10900REVENUEUSD02806.9122792.192
        • 1. Re: Full year view
          edgarernesto.hernandez

          I'm not sure if I understand correctly what you're trying to achieve but using a parameter would seem to be the best solution here.

           

          Filter on value

           

          1. Create a parameter called Filter. This will be a string parameter and you'll create a list of values. You values will be 'ytd_reporting_period_value' and 'qrf_value'.

           

          2. Create a calculated field with the following:

           

          If [Filter]="ytd_reporting_period_value" THEN [ytd_reporting_period_value] ELSE [qrf_value] END


          3. Drop the new calculated field on the desired shelve.


          4. Add the parameter control to the sheet and from there select in the dropdown list what you want to display.


          Hope that helps!

          • 2. Re: Full year view
            Antonio Willybiro

            Thanks you Edgar,

             

            so that's help as a step one.

             

            what I need to add to that is the date parameter.

             

            I have a dimension with dates

            2013.01

            2013.02

            ...

            2013.12

             

            that date dimension is also set as a filter on my dashboard.

             

            from what you gave I need to have that additional feature:

            when the parameter selected is QRF then the dates selected should be all dates from 2013.01 to 2013.12

            but when the parameter select is YTD then the dates selected should be 2013.01 to 2013.06

             

            how to I add this on top of what you have suggested above?

             

            Thanks!

            Antonio

            • 3. Re: Full year view
              edgarernesto.hernandez

              Ah I understand better now

               

              You can try creating the following calculation and also using it as a filter. (I'm assuming your date dimension is a string and not under a date format. If it is, then the calculation will be different so I'll include the two cases)

               

              If date is a string:

               

              IF [Parameter1]="ytd_reporting_period_value"

              THEN

                       IF INT(right([reporting_period],2))<6 THEN 'Show' ELSE 'Hide' END

              ELSE

                       IF INT(right([reporting_period],2))<=12 THEN 'Show' ELSE 'Hide' END

              END

              END

               

              If date is specified as date format:

               

              IF [Parameter1]="ytd_reporting_period_value"

              THEN

                       IF MONTH([reporting_period])<6 THEN 'Show' ELSE 'Hide' END

              ELSE

                       IF IMONTH([reporting_period])<=12 THEN 'Show' ELSE 'Hide' END

              END

              END

               

              You can customize the calculation according to your needs but it's a start. You could even add another parameter to indicate up to which numeric month you want to display.

              • 4. Re: Full year view
                Antonio Willybiro

                Thank you. I am trying to customise this.

                I think the tweak I may have to do there is around those date < or <= because the dates are not something the can select.

                 

                for info, I created a calculated field for the dates called Date

                date("1/"+ STR(IF LEN([reporting_period])=7 THEN RIGHT([reporting_period],2) ELSE RIGHT([reporting_period],1) + "0" END) +"/" + left(str([reporting_period]),4))

                 

                to be continued....

                will let you know how it goes

                 

                thanks again

                • 5. Re: Full year view
                  Antonio Willybiro

                  Hi -

                   

                  so I got it to work, or almost.

                   

                  I am using the following formula:

                  IF [Viewpoint]="Year to Date"

                  THEN

                           IF MONTH([Date])<=6 THEN 'Show' ELSE 'Hide' END

                  ELSEIF [Viewpoint]="Full Year"

                  THEN

                           IF MONTH([Date])<=12 THEN 'Show' ELSE 'Hide' END

                  END

                  The problem I have with this as a filter is that when 'Hide' is selected I get value for July to December thus rest of the year and not full year.

                  I need the second option to show from month 1 to 12 i.e. full year.

                   

                  Any idea how to further tweak the formula to show that?

                  Thanks

                  Antonio

                  • 6. Re: Full year view
                    Antonio Willybiro

                    Any suggestions?

                     

                    Thanks

                    Antonio

                    • 7. Re: Full year view
                      Shawn Wallwork

                      IF [Viewpoint]="Year to Date"

                      THEN

                               IF MONTH([Date])<=6 THEN 'Show' ELSE 'Hide' END

                      ELSEIF [Viewpoint]="Full Year"

                             "Show"

                      END

                       

                      --Shawn

                      • 8. Re: Full year view
                        Antonio Willybiro

                        Beautiful. thank you Shawn!