16 Replies Latest reply: Oct 3, 2012 12:48 PM by haagenfels

# 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

• ###### 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

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

 Month Count({=-"}>}[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
• ###### 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] )

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

• ###### Re: show current and previous 2 months.

Thank you, Very helpful.

Nicholas

• ###### 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

• ###### 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?

• ###### Re: show current and previous 2 months.
 Topic Area NumCompDate ows_ComDate CompDateMonth #Agri Commodity Definition 2011-09-12 00:00:00 9/12/2011 Sep #Agri Swaps 2012-10-12 00:00:00 10/12/2012 Oct #Amend to July 14, 2011 Order for Swap Regulation 2011-12-23 00:00:00 12/23/2011 Dec #Amend to CPO and CTA Regulations Resulting from DF Act 2012-11-05 00:00:00 11/5/2012 Nov #Amend to CPO and CTA Regulations Resulting from DF Act #Ben Ownership Reporting Requirements and Security-Based Swaps 2011-07-16 00:00:00 7/16/2011 Jul #Business Affiliate Marketing and Disposal of Consumer Information Rules 2012-09-21 00:00:00 9/21/2012 Sep #Business Affiliate Marketing and Disposal of Consumer Information Rules #Clearing Exemption for Swaps Between Certain Affiliated Entities #Commodity Options 2012-10-12 00:00:00 10/12/2012 Oct
• ###### Re: show current and previous 2 months.

HI

Try like this

=Count({<DateField={">=\$(=AddMonths(Max(DateField),-2))"}>} CountField )

Its takes max(DateField) for example 05/11/2012 format DD/MM/YYYY

Calculate dated from 05/09/2012 to 05/11/2012 and count the values

Suppose want to calcualte from 01/09/2012 to 05/11/2012 , use MonthStart(AddMonths(Max(DateField),-2))

Hope that helps

• ###### Re: show current and previous 2 months.

Hi,

I tried the expression but again nothing. Keep getting an error message--> "no data to display."

When I use this expression :

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

)

I get a bar graph but with every date from sep-2010 to mar-2014 listed.  The dimension I use is a datemonth field (shows MM-YYYY).  It still is not showing the current date and  past 2 months.

• ###### Re: show current and previous 2 months.

Hi

Instead of using

[ComplianceDateMonth] field  , can you able to use Datefield(i.e. not the monthYear field but date field in the expression)

Hope that helps

• ###### Re: show current and previous 2 months.

Hi,

I changed it to:

Count

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

My expression is DateTest which = Date(ows_ComplianceDate,'MMM-YYYY') as DateTest

Thank you for your help.

• ###### Re: show current and previous 2 months.

Hi .. Look this example ...

I hope it can be useful for you

Gabriel

• ###### Re: show current and previous 2 months.

Thank you.  I have figured it out.

Also thank you Mayil Vahanan.  I would not have understood this without your imput.