3 Replies Latest reply: Aug 11, 2014 2:14 PM by Daniel Hawes RSS

    Past 3 month graph

      I am attempting to have a graph filter for the last 3 months when a month is selected. For example, if April is selected as a filter, then in the graph it will show February, March, and April. I understand the AddMonths function can select previous months if using a negative value, however, it always just selects one. I can't seem to find a way to make it select all 3. I know it is a logic problem, I just can't seem to get the right answer. Any suggestions would be appreciated. Thank you.

        • Re: Past 3 month graph
          Ajay Krishnan Prabhakaran

          Can you attach a sample file please. Its easier to debug.

          • Re: Past 3 month graph
            Manish Kachhia

            Consider that you have below data...

            From Date field we have created MonthYear which can be used in your chart...

             

            =========================================

            Load

              *,

              Month(Date) as Month,

              Date(MonthStart(Date#(Date,'DD/MM/YYYY')),'MMM YYYY') as MonthYear

            Inline

            [

              Date, Sales

              15/08/2013, 120

              22/09/2013, 100

              05/10/2013, 1000

              15/11/2013, 280

              28/12/2013, 75

              01/01/2014, 100

              10/02/2014, 150

              25/03/2014, 250

              11/04/2014, 110

              15/05/2014, 300

              15/06/2014, 600

              10/07/2014, 240

              05/08/2014, 100

            ];

             

            ==============================================

             

            Create a Bar Chart or Straight Table

             

            Dimension

            MonthYear

             

            Expression

            SUM({<MonthYear = , Date = {'>=$(=AddMonths(Max(MonthYear),-2))<=$(=AddMonths(Max(MonthYear),1))'}>}Sales)

             

            ==============================================

             

            Create a list box for MonthYear and try to select any MonthYear...

             

            Hope this is what you want...

              • Re: Past 3 month graph

                It was able to do what I did originally, it shows the first 3 months like I want it to, however, if you click on a month, it just filters down to that month, instead of also showing the 2 previous months according to that month

                 

                EDIT: I was doing some more testing and when you click on a month, it will show the previous 3 months for the month, but it will still keep whatever was last seen, is there a way to get rid of the months after the one clicked one?

                 

                EDITEDIT: So I figured out the problem, had misspelled the second statement, which is why it did not have a max, thanks so much for your help.