6 Replies Latest reply on Apr 2, 2018 7:08 AM by Saikrishna Mamidi

    Balance by end of Quarter

    Saikrishna Mamidi

      Hi,

       

      I have requirement to show Date drill down,

      Suppose If my Data is as follows

      Date                    Value

      1/1/201838
      2/1/201884
      3/1/201858
      4/1/201886
      5/1/201869
      6/1/201853
      7/1/201843
      8/1/201861
      9/1/201835
      10/1/201886
      11/1/201864
      12/1/2018

      70

       

      I would like to see as below if Drill down to Quarter,

      Q1- 58

      Q2-53

      Q3-35

      Q4-70

       

      If I drilldown to month,  it should show full data as on top. Is it possible to do with any QTC.?   Or Do we need to write any complex calculation.   Pl let me know if anyone have a solution.  Thank you in Advance.

        • 1. Re: Balance by end of Quarter
          Ritesh Bisht

          Screen Shot 2018-03-30 at 9.17.25 AM.png

          STEP 1

          Makedate=

           

           

           

          MAKEDATE( INT(RIGHT(STR([Date]),4)),

           

           

          INT ( LEFT(STR([Date]),2) ),

           

           

          INT( MID(STR([Date]),4,2))

           

           

           

           

            )

           

           

           

           

          Step 2

           

          Create Index

           

          Screen Shot 2018-03-30 at 9.18.56 AM.png

           

          and drag it to Rows & Compute Using

           

          Screen Shot 2018-03-30 at 9.19.42 AM.png

           

           

          STEP 3

           

          Filter INDEX =3

           

          Screen Shot 2018-03-30 at 9.17.25 AM.png

           

           

          You will get the answer.

           

          Thanks,

          Ritesh

          1 of 1 people found this helpful
          • 2. Re: Balance by end of Quarter
            Ritesh Bisht

            Oh So you need to DRILL DOWN as well....

             

            Find my new approach then

             

            Screen Shot 2018-03-30 at 9.40.28 AM.png

             

             

             

            Screen Shot 2018-03-30 at 9.40.42 AM.png

             

             

             

            Just one change here

             

            Make a new caclulation , DRAG TO FILTER and set as TRUE

             

            Screen Shot 2018-03-30 at 9.41.56 AM.png

             

             

            Please find solution sheet as attached.

             

            Please mark this thread as CORRECT / HELPFUL if it really helped you so that it can help others as well.

             

            Thanks,

            Ritesh

            2 of 2 people found this helpful
            • 3. Re: Balance by end of Quarter
              Saikrishna Mamidi

              Hi Ritesh,

               

              Thank you for solution,  It helped me to find end of quarter values.  But If I drill down, it didn't work as expected.

               

              And another thing is that, I would like to show the data as Stacked bars (not like cross tab).  I do have category dimension added in attached sheet.  And generally my underlying data would be limited to 2 years. they would like to see as continuous Stacked bar chart, There will be another parameter, I have added as reference line in the work book. Users will change the value to move the reference line

               

              The values showing in attached sheet is summing up (sum of month in a quarter).  If possible can you pl help me with solution in attached sheet.

               

              Thank you.

              • 4. Re: Balance by end of Quarter
                Ritesh Bisht

                Hello Sai,

                 

                 

                VIEW 1

                Screen Shot 2018-04-02 at 9.59.14 AM.png

                 

                 

                 

                 

                VIEW 2

                 

                Screen Shot 2018-04-02 at 9.59.22 AM.png

                 

                 

                Hope it helped !

                 

                File is attached (10.3 Version ) ---Check Sheet name as PART 1

                 

                Let me know if it helped you.

                 

                 

                Thanks,

                Ritesh

                2 of 2 people found this helpful
                • 5. Re: Balance by end of Quarter
                  Ritesh Bisht

                  Please mark this thread as CORRECT / HELPFUL if it really helped you so that it can help others as well.

                   

                  Thanks,

                  Ritesh

                  • 6. Re: Balance by end of Quarter
                    Saikrishna Mamidi

                    Thanks for you solution.  I am looking exactly this.  But still another functionality to be applied .     I need another vertical reference line where *** can move based on Date parameter. 

                                   The reason for this visual is, The underlying data is  "Actuals (till date)  + Forecast"  all together for full 2 calendar years, the vertical reference line is used to cut off the actual s and use forecast ed value for the remaining months,  (The date may be current month or 1 or 2 previous months).  

                    If I make Date as discrete, I am unable to add vertical reference line, So in my workbook, I changed the Date as continuous to make Date parameter to slide across.