Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
Can you attach a sample file please. Its easier to debug.
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...
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.