3 Replies Latest reply on May 4, 2017 1:40 PM by Jaspreet Ghuman

    calculated field - how to do it ?

    Kunal Das

      Table1 -

       

       

      sales

       

       

      order_id Sales

      1 100

      2 200

      3 300

      4 400

       

       

      Table 2

       

       

      Refund

       

       

      order_id Sales

      2 200

      4 400

       

       

      Will leftouter between sales and refund on order id

       

       

      How to calculate following using calculated field

       

       

      Now total sales - 1000

      refund is - 600

      Original Sale - 400

        • 1. Re: calculated field - how to do it ?
          Jaspreet Ghuman

          Hi Kunal,

           

          After you join the 2 datasets on order_id sales field getting the total sales - 1000 and refund is - 600 should be pretty straightforward. For Original sale, create a calculated field in any of the 2 table : sum(sales)-sum(refunds). This field should give the required result which is 400.

           

          Cheers,

          Jas

          • 2. Re: calculated field - how to do it ?
            Kunal Das

            Thank you Jas for your reply-.-. It was my bad that the initial data set was not the correct one, The actual data set would be

            Table1 -

              sales

             

            order_id Sales

            1 100

            2 200

            3 300

            4 400

             

             

            Table 2

             

             

            Refund

             

             

            order_id Refund_index

            2 Y

            4 Y

             

            Will leftouter between sales + and refund on order id

             

            data set would be

             

            order_id Sales order_id(refund)

            1 100 null null

            2 200 2     Y

            3 300 null null

            4 400  4    Y

             

            How to calculate following using calculated field

             

             

            Now total sales - 1000

            refund is - 600

            Original Sale - 400

            • 3. Re: calculated field - how to do it ?
              Jaspreet Ghuman

              Hi Kunal,

               

              In this scenario, first create a calculated field for refunds - if [Sales (Refund)]='Y' THEN [Sales] ELSE 0 END

              Then create second calculated field for original sale using Sales and the newly created calculated field for refunds.

               

              Cheers,

              Jas

              1 of 1 people found this helpful