to show last 12 month in bar chart

[yogitamehta10]

HI All,

i have a requirment , i need to show last 12 months data by defualt.

my expression is :

Count({1<Compliant={'Y'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'N'},

[MONTH YEAR Calender]=,Status=>}[ UID])

my dimension is [MONTH YEAR Calender]

Format of dimension is mmm-YYY.

Plaese help ?

[sunny_talwar]

May be this:

Count({1<Compliant={'Y'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'N'},

[MONTH YEAR Calender] = {"$(='>=' & Date(AddMonths(Max([MONTH YEAR Calender]), -12), 'MMM-YY') & '<=' & Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YY'))"}, Status=>} [ UID])

[yogitamehta10]

not working ..

Please review it ?

Count({1<Compliant={'Yes'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'NEW'},

[MONTH YEAR Calender] = {"$(='>=' & Date(AddMonths(Max([MONTH YEAR Calender]),-12), 'MMM-YYYY') & '<=' &

Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YYYY'))"},Status=>}[MPS TXN UID])

[sunny_talwar]

Can you add this

='>=' & Date(AddMonths(Max([MONTH YEAR Calender]),-12), 'MMM-YYYY') & '<=' & Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YYYY')

In a text box object to see what date range is this showing and what is the format? Is this giving you the right range and right format?

Second thing to check is if you have other date and time related field where you are making selections? If there are other fields (for instance Month or Year), you will have to add them to your expression so that your expression doesn't filter out based on selection in those date and time related fields

Count({1<Compliant={'Yes'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'NEW'},

[MONTH YEAR Calender] = {"$(='>=' & Date(AddMonths(Max([MONTH YEAR Calender]),-12), 'MMM-YYYY') & '<=' & Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YYYY'))"}, Status=, Month, Year>}[MPS TXN UID])

If this still doesn't resolve your issue, then I suggest that you provide more detail as to what isn't working and may be provide a sample to show what the issue is.

HTH

Best,

Sunny

[nickm]

Here is one of many ways to solve this requirement: Add this to your calendar script to create a Rolling 12 month flag - this will create through last closed period - ie: September 2015 - August 2016:

-1 * (%_DateKey>= MonthStart(AddMonths($(vToday), -12)) AND %_DateKey<MonthStart($(vToday)))

as _Flag_R12,

Then add this to your expression :

sum({<_Flag_R12 = {'1'}>} Sales)

If you provide a sample file I can insert and forward to you.

Thx - Nick

[nickm]

Sorry, just add the _Flag_R12 = {'1'} to your current expression.

[#7]

Hi,

You can use a dimension like this one,

=Date( monthstart(firstDate), 'MMM-YYY')

and use the "firstDate" data for get the first day (12 months ago). You can calculate it from the function Today().

I hope you find it useful!