Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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...

View solution in original post

3 Replies
Not applicable
Author

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

MK_QSL
MVP
MVP

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...

View solution in original post

Not applicable
Author

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.