3 Replies Latest reply: Mar 11, 2016 5:16 AM by Mayuresh Athalye RSS

    Logic

    Gourav Sikka

      Hi Team,

      I have a table in this format

       

      Header 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8Header 9
      ParticulersBudgetActual
      Net Revenue1500016000
      Selling exps2000022000
      Net realisation670006543
      other54329876
      expection1000011000
      gain78968976

      We have a requirement to see the report in below Structure.For Current Fiscal year 2015-16..Please Check and help me on this..

       

      Header 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8Header 9Header 10Header 11Header 12Header 13Header 14Header 15Header 16Header 17
      PariculersAprMayJunQ1JulAugSepQ2OctNovDecQ3JanFebMarQ4
      Net Revenue1500160017004800120012001200360011001100110033001300130013003900

      Same For Selling Exps,Net Realisation ,Other, Expection, Gain

        • Re: Logic
          Marco Wedel

          Hi,

           

          can you please explain where the month and quarter related information is coming from?

           

          thanks

           

          regards

           

          Marco

          • Re: Logic
            Stefan Wühl

            I think you need to explain a little more detailed how you come from your Budget and Actual Net Revenue values to the Net Revenue values in your requested result table.

            Can't see any rule how to distribute the values (and the sum of requested result revenue values don't seem to match the input record).

            • Re: Logic
              Mayuresh Athalye

              I think this helps you,

              Loading Script:

              t1:

              LOAD * INLINE [

                  Type, Date, Amount

                  Net Revenue, 4/4/2015, 100

                  Net Revenue, 5/4/2015, 100

                  Net Revenue, 6/4/2015, 100

                  Net Revenue, 7/4/2015, 400

                  Net Revenue, 8/4/2015, 500

                  Net Revenue, 9/4/2015, 600

                  Net Revenue, 10/4/2015, 700

                  Net Revenue, 11/4/2015, 800

                  Net Revenue, 12/4/2015, 900

                  Net Revenue, 1/4/2015, 100

                  Net Revenue, 2/4/2015, 200

                  Net Revenue, 3/4/2015, 200

                  Net Revenue, 5/4/2015, 300

                  Net Revenue, 6/4/2015, 500

                  Net Revenue, 4/4/2015, 100

              ];

              t2:

              load Type, Date,

              pick(num#(mid(Date,1,index(Date,'/')-1)),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as Month,

              pick(num#(mid(Date,1,index(Date,'/')-1)),'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as Quarter,

              Amount

              resident t1;

               

              drop table t1;

               

              Create pivot chart using dimension Type, Month, Quarter with expression as sum(Amount)

              Show Partial Sums should be Checked.

              output is shown like this

              1.PNG