9 Replies Latest reply on Aug 22, 2018 10:33 AM by Sai Sri B

    Prev month % calculation

    Sai Sri B

      Hi Team,

       

      I have blended data sources.

      Primary Data Source to populate the measures

      Secondary data source to restrict the date filter to last 4 months.

       

      As shown in below screenshot, I have a date filter (from secondary data source) with single selection. When I choose a particular date it shows the measure data accordingly.

      Requirement: The measure 'Profit Ratio' should be calculated for its previous month of what I choose.

      As an example: if I choose Dec-17 then the 'Profit Ratio' should be calculated for its previous month that is Nov-17. All other measures should be calculated for selected month only.

      Attached the .twbx

       

        • 1. Re: Prev month % calculation
          Joe Oppelt

          I don't have time to dig into your workbook, but I can tell you this:  When you use a quick filter, it eliminates rows from your table.  You select December, the filter throws away November and your calcs don't have visibility to November.

           

          Use a table calc as a filter instead.

           

          LOOKUP(ATTR([ (what you want to filter on) ]) ,0)

           

          LOOKUP is a table calc function.  When you put that on filters, you can even do "Display filter' and all the individual values would show up in the filter list.

           

          The 0 in the second argument says, "for each row, if the value matches what I ask for, then display it."  So you can select one value or multiple values, just like any filter.

           

          But the key things about a table calc filter is that it only controls what part of the underlying table gets displayed on the sheet, and it leaves the underlying table intact.  Now your calcs will have access to November (or any other month.)

          • 2. Re: Prev month % calculation
            Sai Sri B

            Hi Joe,

             

            As per my understanding, I created a below calculation on date field and adding it into filter. Is this what you have suggested? Please suggest.

             

            • 3. Re: Prev month % calculation
              Joe Oppelt

              Right.  That's the first thing I would try.  Put that on the filter shelf.  Select whatever value(s) you need and see how that works.

              • 4. Re: Prev month % calculation
                Sai Sri B

                Joe,

                 

                This is what I see after adding the calculation in filter.

                • 5. Re: Prev month % calculation
                  Joe Oppelt

                  OK, I have your workbook.

                   

                  So we have a complication in that you are filtering from a secondary data source.  LOOKUP is getting fickle with that.

                   

                  (Do you really need to filter months by using the secondary source though?  Why not just filter months from the primary?  Nonetheless, I didn't go into that here.  I just made the secondary filter work with LOOKUP.)

                   

                  I saved the attached as a 10.2 workbook.  I didn't know what version you are using , but if you're a higher version it will upgrade for you.


                  I made a copy of your sheet to leave your original intact.  In V2 of the sheet I did some things.  First I needed to make a MY calc in your secondary.  Tableau wouldn't do the MY translation in the LOOKUP for me, so I forced a value and now I can use that in the LOOKUP calc.  See [Calculation2] in the secondary source.

                   

                  Next I made [Calculation2] in the primary.  it just pulls the ATTR() value from the secondary.  Finally I made [Calculation3] to do the LOOKUP I originally suggested.


                  I threw that onto filters, and I had to modify the table calc settings to look at TABLE(DOWN).  (By default it wanted to do TABLE(ACROSS).  I also had to change the pill to be DISCRETE. Initially it went on as CONTINUOUS, and that gives us the relative filter interface.  I just wanted individual values to select from, and that's what DISCRETE gives me.

                   

                  So I selected for 12/1/17, and I get the same data you have on your original sheet.

                   

                  Now look at [Lookup last month sales].  This is looking back one "hop" in the table.  But notice the value we get.  Again, by default, tableau does table(across).  So under "Corporate" it found the value for "Consumer", because that's one position back when looking across the table.

                   

                  Go to V3 of the sheet.  Here I modified the [lookup last month] calc to do table(down).  Now we have last month's sales correct.  (And you can prove that by adding 11/1/17 on the filter.  You'll see November's and December's data. and you'll see that in December the [Lookup last month] calc is getting the November value.

                   

                  PS:  You'll notice this is now very slow.  That's a function of the things Tableau has to do to make the table calc filter work across the blend.  It's another reason why I asked if this could actually be done totally in the primary data source.

                  1 of 1 people found this helpful
                  • 6. Re: Prev month % calculation
                    Sai Sri B

                    Thanks a lot Joe for such a nice explanation. It helped me lot.

                    • 7. Re: Prev month % calculation
                      Sai Sri B

                      Joe,

                      To answer your question why did I use Secondary Data Source?  as

                      1. I wanted to restrict the filter drop down to last 4 months

                      2. In a dashboard, if I choose a particular month then the charts should be plotted for last 4 months including selected month.

                       

                      These are the 2 reasons why I did use secondary data source.

                       

                      Please suggest if above 2 points can be achieved using single data source. That would be so grateful.

                      • 8. Re: Prev month % calculation
                        Joe Oppelt

                        I'm assuming that you will update the secondary data source from month to month.

                         

                        You could instead have a parameter that shows the 4 months.  (And for now you will have to update it manually as well, because we don't have dynamic parameters.)

                         

                        The user can select a month in the parameter.  You can make calcs to grab selected-month data and prior month data, and you can limit the displayed months based on the parameter selection.  You wouldn't even need the table calcs any more.

                         

                        The internal value of the parameter selections could be set to the first of each month.  (The display value could be anything you want.)  You would then have a calc such as:

                         

                        If DATETRUNC('month',[Data Source Date]) = [Parameter selection] then [Sales] END

                         

                        That calc would grab all sales for the selected month, and all other rows would be null.

                         

                        Datetrunc takes whatever date is passed to it and truncates it to the first day of the specified date-part (in this case, 'month'.)

                         

                        And this calc:

                         

                        If DATETRUNC('month',[Data Source Date]) = DATEADD('month', -1, [Parameter selection]) then [Sales] END

                         

                        DATEADD adds the specified number of date-part to the specified value.  In this case it would subtract one month.  (And it has the smarts to handle years correctly, so if you pass it Jan-1-2018 and subtract a month, it would give you Dec-1-2017.)

                         

                        That calc would give you all sales for the prior month.

                         

                        And you could have a calc on your sheet for a filter:

                         

                        IF [Data Source Date] >= DATEADD('month', -3, [Parameter selection])

                        and

                        [Data Source Date] < DATEADD('month', 1, [Parameter selection])

                        then 1 else 0  END

                         

                        Put that on filters, select for value = 1, and you would get all rows for the prior three months and up through the selected month on your sheet.  If your calcs don't have to look back further than the selected months, you wouldn't need to do the table calc thing, but if you still needed to get access to the 4th month back you could make the calc like this:

                         

                        LOOKUP( ATTR(

                        IF [Data Source Date] >= DATEADD('month', -3, [Parameter selection])

                        and

                        [Data Source Date] < DATEADD('month', 1, [Parameter selection])

                        then 1 else 0  END

                        ) ,0)

                         

                        (I typed that off the top of my head.  Some debugging might be necessary.)

                        1 of 1 people found this helpful
                        • 9. Re: Prev month % calculation
                          Sai Sri B

                          Thanks Joe for you kind help.