7 Replies Latest reply on Jan 13, 2015 4:20 PM by Robert Greaves

    How to show the current month data along with the last month based on a filter

    Rudra Pratap

      Hi,

       

         I have a requirement.In my dashboard I am showing  top 10 products by sales(as bar chart).I have a filter on month.

          Now what I want is to show the last month average sales for those products as a line in the same graph.

          Like if I select Dec,the graph will show the top 10 products by sales for the month of dec as bar chart and average sales for the month of november for those products as a line in the same graph.

      Any workaround is highly appreciated.

       

      Thanks & regards,

      Rudra

       

       

        • 1. Re: How to show the current month data along with the last month based on a filter
          Shawn Wallwork

          Rudra, is the attached what you're looking for?

           

          --Shawn

          • 2. Re: How to show the current month data along with the last month based on a filter
            Rudra Pratap

            Hi Shawn,

                 Thanks for the reply.The kind of graph that I want is same as that you have provided.

                 The only difference I want here is that the line showing the average should show the last month average not the current month.

             

            Like for the graph that you have provided,If I will select November it is showing the Top 10 products of November along with a line showing that month's(November) average.

             

            But Here I want Top 10 products of November along with a line showing last month's(October) average.

             

            Hope I am clearly understood now.

            Waiting to hear from you.

             

            Thanks & Regards,

            Rudra

            • 3. Re: How to show the current month data along with the last month based on a filter
              Shawn Wallwork

              Rudra, you were clear. Thanks. I'll work on this tomorrow, unless someone else jumps in first.

               

              --Shawn

              • 4. Re: How to show the current month data along with the last month based on a filter
                Jonathan Drummey

                Here's one way to go about it. It's a little tricky for a couple of reasons: One is that we need last month's rows available to calculate an average for this month, another is that the top 10 products can not only change from month to month, a product in one month might not even be in the data for the next month and vice versa.

                 

                I set up the view with a relative date filter to pull the last 3 months of data (since we're in Superstore Sales and it's January, we need to set it to 3 to get enough months of data). Then I set up a series of calculations to identify the top 10 in sales

                 

                One issue with the original view that Shawn had created was that it had a sort on Product Name. This sort is done in the underlying database at the level of Product Name, so products weren't being properly sorted within the month. Therefore, I created a new field for the Month of the Order Date and a set on Month of Order Date & Product Name, and sorted that on Sales/Sum/Descending.

                 

                Now, there are two months in the view, so the Index we'll use to filter for the Top 10 needs to be partitioned such that there are independent values for the two months. This requires an Advanced Compute Using of Month of Order Date/Product Name (in that order), sorted by Sales/Sum/Descending.

                 

                Next the Win Avg Feeder has the following formula: PREVIOUS_VALUE(WINDOW_SUM(SUM([Sales]),0,9)/10). This calc has the same Compute Using settings as the Index, and just computes the average sales for the first 10 rows in each partition. The PREVIOUS_VALUE calc ensures that it is available to every row in each partition, because the set of products can change from month to month. The good news is that this calculation is only made once, so it's fast.

                 

                Next the Prior Win Avg has the formula PREVIOUS_VALUE([Win Avg Feeder]) and that has an Advanced Compute Using of Month of Order Date/Product Name in that order. We don't need to do any sort this time. This returns the same value from the first month to every row.

                 

                Finally, we need to filter out that first month so we're only showing the latest month. The First != 0 field with formula FIRST() != 0 will do the trick, with an Advanced Compute Using of Month of Order Date, Product Name, and At the Level for Month of Order Date. The At the Level part causes the partitioning to be on the position, so the first month is the first in the partition and results in FIRST() returning 0, and then the 2nd month causes FIRST() to return -1 and the filter calc will return True.

                 

                You can see all these fields in the Workout worksheet, and the result in the bar chart.

                 

                Note that if you want to allow the user to choose a month, I'd recommend setting up a parameter and a calc field. The parameter would have month values and when the user chooses one, the calc field would return True for the selected month and prior month and that would be on the Filters Shelf.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: How to show the current month data along with the last month based on a filter
                  Rudra Pratap

                  Hi Jonathan,

                   

                       Thanks for the response.This is exactly what I want.

                        But I am looking for a dynamic approach to the month selection.As you mentioned that one way is to use parameter but that is also a tedious job because everytime my dataset will get refreshed I have to put new data into the Parameter.

                   

                  Thanks & regards,

                  Rudra

                  • 6. Re: How to show the current month data along with the last month based on a filter
                    Jonathan Drummey

                    Hi Rudra,

                     

                    You're welcome! The solution I posted is completely dynamic. The only change you would need to make is to change the date filter to be only the most recent two months instead of three.

                     

                    Jonathan

                    • 7. Re: How to show the current month data along with the last month based on a filter
                      Robert Greaves

                      Jonathan,

                       

                      Why is it that the workbook you saved does not show me any visualizations?  Is it because it did not upgrade to 8.3 properly?

                       

                      I have this problem with virtually every workbook I download on this site.