8 Replies Latest reply on Sep 12, 2017 2:11 AM by Tushar More

    Want to show current month, previous month and trailing 12 month average data without using parameter

    saranya pakkiri

      Hi,

       

      I need to how current month, previous month and trailing 12 month average data without using parameter. To be specific, if i select an date filter for eg.july 2017 it should show cureent month count, previous month count and trailing avg count. Thanks in advance,

        • 1. Re: Want to show current month, previous month and trailing 12 month average data without using parameter
          Tushar  More

          Hi Saranya,

           

          You can do this using data blending.  It's bit tricky though.  Please go through the below thread. This quite a lengthy post but you'l get the desired approach. Simon Runc provided this solution.

          Latest Month, Prior Month, and Previous year same month

           

          So I've started working up a version using a Blended Datasource to act as our "Dynamic Parameter" (as per Jonathan's Blog). So for this Blended Data I've just done a Custom SQL on the original data, to only bring me back a list of all the dates (so not every row). I then (just for this quick experiment!) created a Month Trunc version, to act as our filter. if this method has legs, we can use the Parameter [Month/Rolling] to determine the format that this is returned in. I've also created the "1" blend field in both data sources.

           

          I've then created some calculations using this Blended Month Trunc as our Filter (Dynamic Parameter).

           

          [SR - Selected Month From Blend]

          MIN([SuperStore 10.3 - Dates Only Custom SQL Extract].[List of Dates - Month])

           

          [SR - Sales Current Month]

          IF MIN([Order Date - Month]) = [Selected Month From Blend]

          THEN SUM([Sales]) END

           

          [SR - Sales Previous Month]

          IF MIN([Order Date - Month]) = DATEADD('month',-1,[Selected Month From Blend])

          THEN SUM([Sales]) END

           

          [SR - Sales LY Month]

          IF MIN([Order Date - Month]) = DATEADD('year',-1,[Selected Month From Blend])

          THEN SUM([Sales]) END

           

          and this more exotic one for Rolling (where there is a parameter to choose last 12 or 24 months)

           

          [SR - Sales Current Month Rolling]

          IF FIRST()=0 THEN

          WINDOW_SUM(

          IF MIN([Order Date - Month]) <= [Selected Month From Blend] AND MIN([Order Date - Month]) > DATEADD('month',-[Rolling Period],[Selected Month From Blend])

          THEN SUM([Sales]) END)

          END

           

          So the "SR - Working" tab should help explain what each are doing...but I'll explain the last calculation!

           

          So first we need to have Month in the VizLoD for the calculations to work. This is because blended fields come in as aggregates, so if we don't have Month in the VizLoD. For the 1st 3 calculations this is fine, as it only returns a single value. However for the last one (as you can see in working) we end up with a Sales value for each Month, so in order to get a total (running sum) we need to do the WINDOW_SUM (set compute by Month). However this means it returns the right value, but for every month...like the image below

           

          Let me know if it solves your query.

           

          ~Tushar

          • 2. Re: Want to show current month, previous month and trailing 12 month average data without using parameter
            saranya pakkiri

            Hi Tushar,

             

            Here I am not supposed to use parameters. I have the date filter with underlying data based on that dashboard should react. It would be great if you attach some sample workbook for my reference and am using tableau 10.0 version.

             

            Thanks,

            Saranya

            • 3. Re: Want to show current month, previous month and trailing 12 month average data without using parameter
              Jim Dehner

              Hi

              You can use 3 calculated fields based on Today()

               

              Current month =          if DATETRUNC('month',today()) = DATETRUNC('month',[Order Date]) then [Sales] else 0 end

               

              Past Month =              if DATETRUNC('month', DATEadd('month',-1,  today())) = DATETRUNC('month', [Order Date]) then [Sales] else 0 end

               

              Past 12 month =          (if DATETRUNC('month', DATEadd('month',-11,  today())) <=

                                                      DATETRUNC('month', [Order Date]) and  DATETRUNC('month',today()) >= DATETRUNC('month',[Order Date])then [Sales] else 0 end )/12

               

              It will yield this

               

              Let me know if this helped or provided a correct answer

              Jim

              • 4. Re: Want to show current month, previous month and trailing 12 month average data without using parameter
                Tushar  More

                Hi,

                 

                You can use Jim's solution.

                If you want to use data blending as suggested by Simon as posted in my thread then perform the following steps.

                 

                1.Connect to your original data source.

                2.For blending, write a Custom SQL on the original data, to get only the date field. This will be our secondary data source.

                3.To blend these data source, create a dummy calculation in both data sources.

                4.Blend these data sources using the above calc.

                5.Create a calculation to trunc the date field to get months in the secondary data source.

                6. Place this truncated date field in the filter shelf.

                7.Create truncated version of the date field in the primary data source.

                8. Now, to get current month, prev month, create some calculations using the truncated date field (created in secondary DS) in the primary data source.

                 

                Sales Current Month:

                IF MIN([Order Date - Month]) = [Selected Month From Blend]

                THEN SUM([Sales]) END

                 

                Sales Previous Month:

                IF MIN([Order Date - Month]) = DATEADD('month',-1,[Selected Month From Blend])

                THEN SUM([Sales]) END

                 

                Sales Previous Year

                IF MIN([Order Date - Month]) = DATEADD('year',-1,[Selected Month From Blend])

                THEN SUM([Sales]) END

                 

                Place these columns in the view and do not forget to place truncated date field created in the primary data source to detail.

                 

                Here is the final view.

                 

                I am v 10.3 so not attaching the workbook.

                 

                Let me know if you face any problem.

                 

                ~Tushar

                2 of 2 people found this helpful
                • 5. Re: Want to show current month, previous month and trailing 12 month average data without using parameter
                  saranya pakkiri

                  Hi Tushar,

                   

                  Thank you for reply. I am close to the solution but for previous month calculation I did not get value. And i need to show trailing average of last 12 months instead of previous year sales.Could you please help me out.

                   

                  Thanks,

                  Saranya

                  • 6. Re: Want to show current month, previous month and trailing 12 month average data without using parameter
                    Tushar  More

                    Hi Saranya,

                     

                    Attached is the solution workbook created in v9.3. Please check and try to match your formulas with the formulas in my workbook. I updated the calculation to get trailing 12 months data.

                     

                    There a couple of things that you'll have to do once you create your view.

                    1. Make sure to turn off stack marks.

                     

                    2. To get trailing 12 months, you've to set Compute using to Order date-Month.

                     

                    Let me know if this resolves your problem.

                     

                    ~Tushar

                    • 8. Re: Want to show current month, previous month and trailing 12 month average data without using parameter
                      Tushar  More

                      Glad it worked.

                       

                      Could you please mark an answer as correct and helpful to close this thread.

                       

                      ~Tushar