7 Replies Latest reply on Jan 28, 2019 12:56 AM by Shivangi Aggarwal

    Urgent

    Shivangi Aggarwal

      Hi ,

       

      I have a stock data as shown below.

      Screenshot (33).png

      I want to calculate Difference between close-open. value of close should be choosen as --- close value corresponding to last date of stockname (MAX(D)) and open value should be ---- open value corresponding to first date of stock (MIN(D)) .there are different types of stock table . I am unable to fetch this data . Please help me as soon  as possible as its really urgent.

       

      I am able to execute the same in POWER BI in this way

       

      ClosingAmount =

       

      VAR maxDate = MAX(Stocks[Date]) RETURN

      CALCULATE(sum(Stocks[Close]),Stocks[Date]=maxDate)

       

      but not able to execute i tableau

        • 1. Re: Urgent
          Naveen B

          Max Close Value:

          Calc: IF {Fixed stockname: max(D)}=D

          THEN Close END -> this will give the close value of max date per store name

           

          Min Open Value:

          Calc:IF {Fixed stockname : MIN(D)}=D

          THEN Open END --> This will give the open value of min date per store name

           

          Difference

          Max Close Value - Min Open Value

           

          Hope this helps.

          Plz mark this answer as correct or helpful to close the thread

           

          If you want filters to affect the calculation add the filter to context.

           

          Check tableau order of operation in google it will help you understand the Filter stages and when they will apply.

           

          Regards,

          NB

          1 of 1 people found this helpful
          • 2. Re: Urgent
            Chris McClellan

            Any chance you can supply the data file to help with ?

             

            I don't see how that Power BI syntax is going to work, there's no column name [Date}

            • 3. Re: Urgent
              Shivangi Aggarwal

              Hi ,

              Thanku for Your quick reply . I am able to get MAXCLOSE VALUE and MIN OPEN value but not getting difference when apply calculation field if difference its not showing any result . Actually I am very much new to Tableau . Screenshot (34).png

              • 4. Re: Urgent
                Naveen B

                change your difference calculation to

                SUM(Max closed ) - SUM(Min Open)

                 

                Instead of Max closed - open

                 

                BR,

                NB

                1 of 1 people found this helpful
                • 5. Re: Urgent
                  Shivangi Aggarwal

                  Hi, I am bit confused why we are using Aggregation function sum while calculating the difference . Acc. to me SUM(Max closed ) - SUM(Min Open) it will first calculate the sum of MAX CLOSED then calculate the difference . I am not getting how it works . 

                  for example:-

                   

                  I have to calculate the gain percent - should i write like this :-

                  ([DIFFERANCE]/SUM([MINOPENVALUE]))*100

                  • 6. Re: Urgent
                    Naveen B

                    yes you should aggregate the data then fine the difference

                     

                    (SUM(Max closed ) - SUM(Min Open)/

                    SUM(Min Open))*100

                    • 7. Re: Urgent
                      Shivangi Aggarwal

                      Hi ,

                      But why aggregation is required ?