Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
GabrielAraya
Employee
Employee

Hi .. Look this example ...

I hope it can be useful for you

Gabriel

View solution in original post

16 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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
Author

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
Author

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_Michaelides
Luminary Alumni
Luminary Alumni

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
Author

Thank you, Very helpful.

Nicholas

Not applicable
Author

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.

MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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,

MayilVahanan

HI

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.