show current and previous 2 months.

[#1]

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

[Jason_Michaelides]

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

[#3]

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.

Month	Count({<Month={">=-"}>}[Month] )	count (Month)
1899-12	0	1172
2011-08	0	4
2011-09	0	5
2012-01	0	48
2012-04	0	26
2012-05	0	21
2012-06	0	8
2012-07	0	14
2012-08	0	5
2012-10	0	19
2012-11	0	77

[#4]

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]

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.

[#6]

Thank you, Very helpful.

Nicholas

[#7]

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

[#9]

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?