3 Replies Latest reply on Jul 24, 2017 5:52 PM by Zhouyi Zhang

    Pre/post period comparison using date parameters

    Jeff Nelson

      Hi all,

       

      I am trying to achieve summary statistics for two different time periods using parameters, and subsequently compute the difference and variance between the periods. Much of the help I have seen on the community forums either restricts to one time period, or does not allow the user to choose between measures.

       

      I've attached a sample workbook using the Superstore data. You'll see there are 4 parameters for pre/post start and end dates. Additionally, there is a parameter that allows a user to select the dimension he or she is interested in comparing between the two time periods. The calculated fields "Pre Date Measure" and "Post Date Measure" use Level of Detail functions to display a measure by Category and Pre/Post designation. The difference and variance are then calculated using these two columns. In order to get Difference working, I need to use the ZN function to set zero values to NULL. Then, when I calculated variance, I always get a value of 100%. This seems weird, because variance is little more than (difference / pre-period), and the values for both difference and pre-period populate just fine. I suspect this issue might have to do with the use of the ZN function? Is Tableau setting some value to NULL or 0?

       

      Does anybody know what is happening here? Is there a solution to show the correct variance metric?

       

      Note my .twbx is in 10.3. I might be able to downgrade it if necessary.