4 Replies Latest reply on Jan 13, 2017 9:58 AM by Stephen Rizzo

    Month over Month displaying only current month

    michaels.kale

      I'm having a bit of an issue. I need to calculate the difference of Month over Month and display the results as an up/down arrow. I had been doing this difference using:

       

      SUM([BWPages]) - LOOKUP (SUM([BWPages]), -1)

       

      However, this only works if you display data for previous month as well. What happens if I want to display only the current month? If I filter for the top 1 month then it filters out all data except for that month and can't do a lookup. Ideally I would want to display one arrow on my dashboard for this month that shows if I did better or worse than last (without being forced to show last month data).


      Thanks!

        • 1. Re: Month over Month displaying only current month
          Walt Reed

          Hey Michael,

          First, I would create a parameter that you will use to filter your date range (in my example, I'm using the month name):

          Then, I create a logical calculated field that basically filters out the other months while keeping the value needed for the MoM calculation:

          Add this to the filter shelf and select true. Now, the viz should show only the month you want to see, along with the MoM calculation:

           

          I hope this helps!

          Walt

          • 2. Re: Month over Month displaying only current month
            Stephen Rizzo

            The problem is that LOOKUP is a table calculation function. Table calculations do further calculations on the data displayed in your viz - if you filter out the previous year prior to the table calculation being computed (regular dimension / measure filters are computed before table calculations), then LOOKUP will not find the previous year's data.

             

            You may be able to work something out by filtering using a table calculation instead of your normal year filter though. Try creating the calculated field [Index] : INDEX(), then filtering on [Index] = 1. If you set index to compute using your year variable, Tableau may calculate the LOOKUP prior to the data being filtered.

            • 3. Re: Month over Month displaying only current month
              Randi Gauthreaux

              Could provide more detail on your solution? I'm not advanced with Tableau calcs and I don't know what to INDEX. I created the parameter, but can't get a valid calc.

              thanks!

              • 4. Re: Month over Month displaying only current month
                Stephen Rizzo

                Sure, I can elaborate. First set up your viz the way you want it without the "most recent month" filter. Then create the following calculated field

                 

                [Index] : INDEX()

                 

                Pull this calculated field into the "Filters" area and keep only the value 1. Then right click on the [Index] pill in the Filters card, and under Compute Using, select [Order Date]. This will keep only the first year / month / day / other unit of order date displayed in your table. You may need to change the sort on [Order Date] in order to get the most recent month to show as opposed to the first month.

                 

                Basically what the INDEX function does is enumerate the displayed values according to whatever is set in the "Compute Using" setting. I would encourage you to read through the following article for more information on how Table Calculations work in Tableau:

                 

                Table Calculations: Addressing and Partitioning