8 Replies Latest reply: May 14, 2012 10:24 AM by Robert Angel RSS

    FRS - Prompt / Y-T-D.

    Robert Angel
      Hi,


      I have a report with numerous columns, one of which gets the current period via a prompt - to use in a column that has that periods data, several of which reference this prompt, so I do not ask the user for the same info numerous times.

      My problem is that I want to use YTD functionality in conjunction with the prompt from another column - I can find a way to use either one, but not both together - I know this should be simple, can someone help me out please - I know the what just not the how, so a step by step would be helpful...

      i.e. I want the equivalent of; -


      Y-T-D Same As Column A

      Is this possible?


      thanks,

      Robert.

      Edited by: Robert Angel on 03-May-2012 06:09
        • 1. Re: FRS - Prompt / Y.T.D.
          Iain Curtain
          Rather than specifically pull the YTD member, can you use the prompt in a range function?

          Something like Range from Jan to Prompt?

          Cheers, Iain
          • 2. Re: FRS - Prompt / Y.T.D.
            Robert Angel
            Hi,


            I just wind up with the same problem in a different way - I can pull the range function no problem, but I cannot reference the other column or prompt within the function - unless I am missing something obvious??

            thanks for your input,

            Robert.
            • 3. Re: FRS - Prompt / Y.T.D.
              Rowdy
              Does it make sense to use 'current POV' as end member selection in your report with Range function?
              • 4. Re: FRS - Prompt / Y.T.D.
                Robert Angel
                No, sorry.

                I need to have FULL YEAR, Single Period, Y-T-D Period, average(Y-T-D Period) and FULL YEAR / 12 in the columns - so my POV does not / cannot include the period as none is relevant to all columns, it is this 'bombay mix' of timeframes in the same report that is giving me the headache.


                regards,

                Robert.
                • 5. Re: FRS - Prompt / Y.T.D.
                  Rowdy
                  <Column>.....................A...............................B.........................................................C....................................................D............................E
                  <requirement>..............FullYear.........................SinglePeriod.......................................YTD Period..............................Average (Y-T-D period).....FullYear/12
                  <member selection>....IDESC(YearTotal)....."Curr Uer POV (enable prompt in POV)"....Range(start-Jan & End-curr POV).......................C/CountC.......................A/12


                  How does this look? You user selection (Prompt) will drive Columns B,C & D and you will be hard coding seletions (as it is static) in Column A & E.
                  • 6. Re: FRS - Prompt / Y.T.D.
                    Robert Angel
                    Hi,


                    yes - I know this is what I need to do, it is getting the tool to let me put the Y-T-D 'around' the prompt that is my value - i.e. not what to do, but physically how to be able to select these together, the tool does not let me put one in the other - can you provide a brief example of the navigation path involved just to access an existing prompt with YTD in the same column - this is what is eluding me...

                    My version is 11.1.2.507

                    thanks again,

                    Robert.
                    • 7. Re: FRS - Prompt / Y.T.D.
                      Rowdy
                      With the layout I mention you don't need to select DTS members. See below steps to get YTD assuming that users will enter current month in the prompt.
                      Insert a data column
                      Click on 'functions' tab in member selection area
                      Select range function
                      When prompt window pop-up select start member as Jan and end member as 'current POV for period'
                      *Insert a formula column and use formula with syntax [column] : ex [D] and hide the data column with range function*
                      repeat the same steps or insert an other formula column with [D]/Column[D].countA to get average YTD value.


                      Sorry if I am totally missing anything here.
                      • 8. Re: FRS - Prompt / Y.T.D.
                        Robert Angel
                        Sorry for the delay in replying, pressure of other areas of work....


                        What I am trying to get is; -

                        Given that; -

                        Periods: April to March
                        Dynamic Time Series Exists on Periods
                        Data Type: WTE (Whole Type Equivalent)

                        Desired Column

                        Average WTE (Given that user select June in prompt for period which is the 3rd period in my year)

                        YTD(WTE) / 3

                        Without harding coding 3 / writing a seperate report for each period - is this possible??

                        (Given that I also want sum(Apr to Mar) based figures in the same report, single period (June) figures and 'straight' Y-T-D figures)



                        thanks again,

                        Robert.