Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

show current and previous 2 months.

Hi,

I am trying to create a bar graph that shows the count(month) but does not show any data from two months prior to the current date. I have my dimension as Month and Expression as count(month).  That shows the graph with the correct numbers but i would like to only show the previous two month from the current date. I tried to create another expressions Sum({<Month={"$(=Max(Month)-2)"}>}[Month] but I think its wrong.

Any help is appreciated.

Thank you

Tags (2)
1 Solution

Accepted Solutions
Employee
Employee

Re: show current and previous 2 months.

Hi .. Look this example ...

I hope it can be useful for you

Gabriel

16 Replies
jason_michaelid
Honored Contributor II

Re: show current and previous 2 months.

Almost right, I think:

Count({<Month={">=$(=Max(Month)-2)"}>}[Month] )

Or better still, if you're on v11 use dimension limits.

Hope this helps,

Jason

Not applicable

Re: show current and previous 2 months.

I am still seeing all the months listed in the graph. Should the expression show both items? Count(Month) and

Count({<Month={">=$(=Max(Month)-2)"}>}[Month] )? I tried with one and both and no changes seem to occur.  I have the dimension still with just month.  Below is the data i get when i export the graph to excel.

MonthCount({<Month={">=-"}>}[Month] )count (Month)
1899-1201172
2011-0804
2011-0905
2012-01048
2012-04026
2012-05021
2012-0608
2012-07014
2012-0805
2012-10019
2012-11077
Not applicable

Re: show current and previous 2 months.

A few suggestions:

1) You'd better use month-year instead of simply month, othervise you will face problems crossing year borders.

2) Use AddMonths function to get earlier/later months.

E.g.

Count({<MonthYear={">=$(=AddMonths(Max(MonthYear),-2))"}>} [MonthYear] )

jason_michaelid
Honored Contributor II

Re: show current and previous 2 months.

Good advice from Dmitry, and also it looks like your "Month" field is not actually a numeric date field. Before AddMonths() will work you need to ensure "Month" is a date field.

Not applicable

Re: show current and previous 2 months.

Thank you, Very helpful.

Nicholas

Not applicable

Re: show current and previous 2 months.

I thought i had this down but seemed to have lost it. My graph just shows a blank.  I am using QV 10, so i do not have the dimensions finction available. When I use your recommended expression

Count({<MonthYear={">=$(=AddMonths(Max(MonthYear),-2))"}>} [MonthYear] )  I am just getting a blank.  My Dimension has the month listed in it.

Sorry and thanks again.

Re: show current and previous 2 months.

Hi

Can you able to say the MonthYear format?

Because Addmonths() gives like 11/11/2012 ..

MonthYear is also same format? i.e DD/MM/YYYY

Not applicable

Re: show current and previous 2 months.

Im not sure I understand what you are looking for exactly.  I have this in my script to make sure it is coming through as a date format.

 

Date#(CompDate,'DD/MM/YYYY') as NumCompdate,

Re: show current and previous 2 months.

HI

MonthYear format?? Can you give a sample value for MonthYear?

Community Browser