Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

Re: Past 3 month graph

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

3 Replies
Not applicable

Re: Past 3 month graph

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

MVP
MVP

Re: Past 3 month graph

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

Not applicable

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.

Community Browser