4 Replies Latest reply on Aug 1, 2016 5:26 AM by Anshul Gupta

    Calculating dynamic sheet w.r.t previous values and getting min on calculation

    Anshul Gupta

      Hi All,

       

      Sorry for the long subject line.

      I need to calculate dynamic data w.r.t the previous data. Some of the parts are calculated but all the calculation is based on base numbers. I have attached an excel sheet which contains Current data and required data including the formula for each cell (under Required Output area). I am attaching workbook too with some of the calculations. Please help me out with the solution.

       

      Thanks

        • 2. Re: Calculating dynamic sheet w.r.t previous values and getting min on calculation
          Jonathan Drummey

          Hi Anshul,

           

          I took a quick look at this and it's technically possible to do this in Tableau, however

           

          a) It's much easier to compute this in Excel because Excel can do arbitrary cell references.

           

          b) The year grand total showing a single value is not something that is easily displayed in Tableau because of how Tableau does layout (it doesn't merge columns or rows like Excel does).

           

          This is one of those cases where letting Excel do what it's good at and not forcing Tableau into something it's not good at might be the better choice. I know we don't always have that option, but it's worth exploring in your use case.

           

          I'll solve this when I have time, it'll take a couple-few days before I can get to it (I've got to take care of my paying clients first . A few questions in the meantime:

           

          1) Is the layout of the Required Output field in Excel the true output, or is this an in-between output while the data is further manipulated and/or a different chart type is used (such as a line chart, heat map, etc.)

          2) Are there quarters when the data is sparse, i.e. no records for a given Sub-Category?

          3) What is your data source? Excel, SQL Server, etc.?

          4) Are you able to build a custom view or query on that data source?

          5) What is the actual # of sub-category equivalents in your view?

          6) What is the underlying number of records?

           

          Jonathan

          • 3. Re: Calculating dynamic sheet w.r.t previous values and getting min on calculation
            Anshul Gupta

            Hi Jonathan,

             

            Thank you for your reply, below are the answers for your questions:

             

            1. Those Outputs are the true outputs which I need to show in cross tab structure as well as in bar graph also (as per client requirement).

            2. There can be some sub categories in an quarter which does not have any data.

            3. My data source is postgresql.

            4. We can build a custom SQL.

            5. Currently its 10, in future it can be more, so I am will to make it dynamic.

            6. Around 50K.

             

            Will try to resolve this in a meantime. Please let me know if you are able to make it.

             

            Thanks again!!

             

            Anshul

            • 4. Re: Calculating dynamic sheet w.r.t previous values and getting min on calculation
              Anshul Gupta

              Hi Jonathan

               

              Did you get a chance to try it.. I am sorry for stretching you in this... But I really need a help!!

              I tried it, the addition of with first column digit and base digit is taking place by using: ZN(SUM([Quantity])) + ZN(LOOKUP(SUM([Quantity]), -1))

              But subtracting column's min is getting difficult. Can you please help me out!!

               

              Thanks in advance.

               

              Anshul