8 Replies Latest reply on Dec 30, 2019 1:29 PM by Steve Wood

    Circular Reference Error in Sequential Calculations (Epidemiological Modelling)

    Steve Cantrill

      As a relative newbie at Tableau, I wanted to spend some part of my Christmas holiday gaining a better understanding of Tableau’s calculation powers.


      In the attached example I have tried to create a basic epidemiological model. The data source contains a single column containing the values from 0 to 10. Other values are input through a series of parameters.


      The model relies upon sequential calculations of Ct+1 and St+1, and therein lies the problem - the calculations create circular reference errors which are compounded through the rest of the model. I have tried using both Lookup and Previous_Value, and table calculations but have not been able to resolve the circular reference error. I would appreciate any assistance you can provide.

      Tableau Circular Reference.PNG

        • 1. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
          Rangaraj Singaravelu

          Dear Steve,


          Greetings of the day.


          May I know the detailed description from the below mentioned snap shot



          As per above Calculation filed (Field name is [St+1] but in inside the calculation you are using the [St+1]


          Could you please explain the logic to compute the St+1 and Ct+1


          Can you please mentioned your required output please.


          Thanks in Advance,


          Rangaraj S

          • 2. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
            Mavis Liu

            Hi Steve,


            Are you trying to do an iteration? Because Tableau doesn't quite do that, as Rangaraj says, if you let us know what you're trying to achieve we can try and help you better.





            • 3. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
              Steve Wood

              Hi Steve C and others,


              I was intrigued by this and ended up reading up on SI / SIS / SIR models last night; fascinating!


              I'd echo the comments above about needing a bit more info on the underlying calculation and expected results. I did come across the following post on python versions of the various models, and it may be helpful:




              I'm not 100% sure if you're trying for a SI or SIS model Steve, or something different? If someone can confirm which, then the python might be easier to translate to Tableau than the other mathematical equations that came up in searches! Assuming it is correct...


              Also wondered if this blog on PREVIOUS_VALUE is useful ... although I couldn't quite see how to apply it to the trickier situation here (echoing Mavis' point re iteration probably). My guess is you already saw something like this article Steve?





              • 4. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
                Deepak Rai

                you have not defined Ct+1  yet. First you need to define and then use it in St+1

                • 5. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
                  Steve Cantrill

                  Hello all


                  Thanks for your interest in this problem. Hopefully, I have answered each of your questions below.


                  I have attached an extract from the book in which I came across this model which gives all of the details that I have. I honestly don’t know whether this is a SI, SIS or SIR model. It intrigued me to be able to reproduce it in Tableau given that most of the inputs are parameters and the data source simply provides placeholders for the calculated fields.


                  The problem is how to calculate the successive values of ‘Cases of infection’ (C) and ‘individuals Susceptible to the infection’ (S). In the workbook these are the calculated fields St+1 and Ct+1. They denote S(at time 1), S(at time 2), S(at time 3)… and C(at time 1), C(at time 2), C(at time 3)…. Ct and St are the values at time 0. Since the current value of S and C rely upon what has happened in the past, the previous value of S and C are used in the calculation. For example:


                  S3 = S2-(C3-C2)     [susceptibles fall by the same value as cases increase]


                  C3 = S2*(1-(1-(p^C2)))     [probability of contact changes as cases increase and susceptibles decrease]


                  The input parameters cover the values of:

                  N – the initial population size

                  K – the number of adequate contacts within a serial generation

                  t – the Serial Generation time period

                  St – the initial number of susceptible individuals in the population (S at time 0)

                  Ct – the initial number of individuals with the infection (C at time 0)


                  These values are used to calculate

                  I – the number of immunes (N – St) [which, on reflection, may not be calculated correctly]

                  p – the probability of contact with another individual that is adequate to pass on the infection (K/(N-1))

                  days passed – Serial Generation (from Data Source) * Serial Generation Time (in days)


                  I have also copied the parameter values of Ct and St to calculated fields.


                  I found an error in the calculation of Ct+1 and so have attached an updated workbook

                  • 6. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
                    Steve Wood

                    Thanks Steve! I reckon that will help us home in on something.

                    Because you've mentioned I (number of immunes), I'm going to take a punt on this being SIS SIR - not sure if that helps much of course!

                    Ta, Steve (W)

                    • 7. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
                      Steve Cantrill

                      Hi Steve W


                      I have reworked some of the maths in the model and have been able to produce the attached workbook.


                      Unfortunately, this method requires that the incremental changes are each calculated in their own field. So, rather than just having a data source that is long enough for the serial generations I want to model, I have to create 5 calculated fields for each serial generation. This also becomes a problem for visualising changes over time.


                      I still think the original method was preferable, even if I may have to adjust some of the maths. There has to be a way of doing sequential calculations as they are so much a part of population dynamics and modelling workforce change.




                      Steve C

                      • 8. Re: Circular Reference Error in Sequential Calculations (Epidemiological Modelling)
                        Steve Wood

                        Hi Steve,


                        I've made some progress, and can get some form of interdependent sequential calculation to work, but not quite the one you defined. Possibly that's just that the calculation is a little off, so I'll attach where I got to in the hope that you, or someone else, can take it further.


                        The concept is based on a previous forum post I found, so a massive hat tip to Noah Salvaterra on that front. Here is the link to the post: Recursion with multiple initial values?


                        The idea is to maintain the two interdependent values in a delimited string. That way PREVIOUS_VALUE gives you access to both values.


                        Sn|Cn (simple) is the example I got working in the screenshots below. It says that susceptible population reduces by the previous number of cases, whilst the number of cases increases by 1% of the previously susceptible population. I also tried Sn|Cn using your calc and included the final separation out into Sn and Cn values (on the right), but don't feel like I've ended up with the right numbers. Still, feel free to look at that calc too in the attached.

                        the "simple" calculation is reasonably well commented so you can see my thought process - and either extend it or have a good laugh ;o)



                        PS. Whilst digging into this more I noted comments suggesting that something like Tableau's R or Python integration might be a more appropriate mechanism for this sort of calculation. I guess offloading the calc to a data prep step like Python, or maybe Tableau Prep could be an option too. Still, it was fun wondering if it could be done directly in Tableau Desktop itself!