8 Replies Latest reply on Feb 22, 2016 1:36 AM by Simon Runc

    PREVIOUS_VALUE() reaching higher than 1 row before

    Tomek Zbrozek

      Hey,

       

      Does anyone know if there's a way to self-refer to a table calculation, but reaching more than just one row before. E.g. I need to self -refer my table calculation to a row 10 rows before.

       

      I think that PREVIOUS_VALUE() can be used only to access one row before.

       

      Thanks!

        • 1. Re: PREVIOUS_VALUE() reaching higher than 1 row before
          Simon Runc

          hi Thomas,

           

          It'll depend on the shape of your data, level of VizLoD...etc. but you can either use LOOKUP, or WINDOW_SUM/AVG...

           

          For LOOKUP you can define how far back

           

          So for example

           

          LOOKUP(SUM([Sales], -10) brings back the value 10 partitions back

           

          For Window_Sum (or all the WINDOW_XXX calcs) you can define the start and end points

           

          WINDOW_SUM(SUM([Sales]),-10,0) will SUM up the previous 10 partitions back. If you look at my answer here you can see this in action Re: Rolling Months

           

          Hope this helps, but please post back if this a) doesn't make sense b) doesn't solve your issue (in which case a sample of your data would help)

          1 of 1 people found this helpful
          • 2. Re: PREVIOUS_VALUE() reaching higher than 1 row before
            Simon Runc

            Looks like you beat me to it!...a good use-case for the 'I got this one button'!!!!

            1 of 1 people found this helpful
            • 3. Re: PREVIOUS_VALUE() reaching higher than 1 row before
              Tomek Zbrozek

              Thank both of you guys for your initiative to answer my question. I'm afraid it doesn't solve my problem.

               

              Here is my data (Axis_x_position is a table calculation I want to create, please see the Excel file attached to inspect formulas. There are some different formulas in this column and what I want to do is to pack them into one calculated field, which will be my X axis to draw something like this:

               

              Building barchart treemap (revamping Radial Tree into non-circular shape

              ). Please mind that LOOKUP() does not allow to make a self-reference which is possible to do using PREVIOUS_VALUE().

               

                

              LevelCategorySizeAxis_x_position
              1Category_110000
              2Subcategory_1a150
              2Subcategory_1b2015
              2Subcategory_1c2535
              2Subcategory_1d11060
              2Subcategory_1e300170
              3Sub-subcategory_1ea50170
              3Sub-subcategory_1ab150220
              2Subcategory_1f10170
              2Subcategory_1g35180
              2Subcategory_1h215215
              2Subcategory_1i5430
              1Category_23001000
              2Subcategory_2a101000
              2Subcategory_2b551010
              1Category_33501300
              1Category_410201650

               

               

              Hope that my description makes sense!

              • 4. Re: PREVIOUS_VALUE() reaching higher than 1 row before
                Simon Runc

                hi Tomek,

                 

                ...So yes that's a bit of a different problem!

                 

                Just so I'm clear, you are trying to re-create the Axis_X_Position, from your Excel but in Tableau? If so what is the 'rule' for when the formula changes from Adding the Previous 2 Rows, To referencing several rows above, to only using the Size from the Previous Row...etc. I ask this as in Tableau we need a 'Rule' as to when a formula needs to do something else.

                 

                by way of why...I'll refer you this excellent, and insightful post by Jonathan Drummey, on the differences between Excel and Tableau....

                Re: We don't need "Dynamic" Parameters.

                Where this connects to this thread is that there are multiple mental models (paradigm) that people come to Tableau with, here are three:

                 

                - "cell-based" that comes from VisiCalc and its descendants, most notably Excel. We effectively operate on one cell at a time and can chain results together.

                - "variable-based" that comes from programming. In this model variables are defined and operated over so an operation could be something like "get the value of parameter Alpha, compare it to record 1, if it matches then return X, then compare to record 2, and so on". Writing database application code using cursors is using this paradigm.

                - "set-based" that comes from databases. This is where operations are performed "at once" across sets of records, like SUM([Sales]).

                As a set based paradigm we can't do things, without a rule-set, to do say C5+J9+K12...etc. as we can in Excel (this is also the reason btw we can run multi-million row calculation is milliseconds, compared with Excel which struggles above 200k rows!!)

                1 of 1 people found this helpful
                • 5. Re: PREVIOUS_VALUE() reaching higher than 1 row before
                  Tomek Zbrozek

                  Thanks Simon!

                   

                  Please find attached the Tableau workbook with expected view (showing a structure of flow on website, by categories) and current view which is still far from expected... You can see the formula for axis calculation inside calculated field - I also explained as a comment in calc field, where I would like to use the self-referring (not existing! ) function. Maybe you'll be able to help out with this!

                   

                  Just as a reminder, this is a continuation of this question Building barchart treemap (revamping Radial Tree into non-circular shape)

                  Still trying to de-circulate radial tree.

                   

                  Thanks!

                  • 6. Re: PREVIOUS_VALUE() reaching higher than 1 row before
                    Simon Runc

                    Hi Tomek,

                     

                    Yes that makes sense...and I can see you have the correct values in the Table Version. I'll have a play (might be next week now) and let you know how I get on. One thing that immediately sticks out, from the fact you have the Table Version working, is the 'Order' of the Categories (which you manually sorted for the Table) needs to be same in the Viz (as the PREVIOUS/LOOKUP depend on that order to reference back to the correct row). I changed this in your Viz and immediately got the Level 3 looking right...Level 1 & 2 still looks odd so I'll have a look into why.

                    1 of 1 people found this helpful
                    • 7. Re: PREVIOUS_VALUE() reaching higher than 1 row before
                      Tomek Zbrozek

                      Thanks for sharing your ideas. I finally managed to solve this problem and build something I call 'Gantt treemap'. You can see the effect in a workbook attached.

                       

                      I think this chart is good to show structure or flow, reducing the number of marks displayed (e.g. comparing to Radial Treemap).

                      1 of 1 people found this helpful
                      • 8. Re: PREVIOUS_VALUE() reaching higher than 1 row before
                        Simon Runc

                        Nice work Tomek...and thanks for sharing.

                         

                        Yes I think this is visually 'easier' to read that the radial plot too...Just got to find a project to 'shoe-horn' this Viz-type into!!