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

[#2]

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

[MayilVahanan]

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

[#4]

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. 

[MayilVahanan]

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

[#6]

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.

[GabrielAraya · Accepted solution]

Hi .. Look this example ...

I hope it can be useful for you

Gabriel

[#8]

Thank you.  I have figured it out.

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