10 Replies Latest reply: Oct 19, 2016 6:49 AM by Sunny Talwar

# Set Analysis for Prior Month

Hi All,

I have field called sys_created_on. I have calculated Year Month on basis of this. Below is my expression:

Date( MonthStart( sys_created_on ), 'YYYY-MM') as [Month_Year1]

I extracted max Month using    set v2= max(Month_Year1);

Now I want to see value for prior to max month. I have used below expression:

Count({<Month_Year1 ={"=Date(addmonths(\$(v2), -2), 'YYYY-MM')"} >} number).

But it is always showing current month data only. Not prior to max month.

Please help.

Thanks,

Sarif

• ###### Re: Set Analysis for Prior Month

Try one of these:

Count({<Month_Year1 ={"\$(=Date(AddMonths(v2, -2), 'YYYY-MM'))"} >} number)

or

Count({<Month_Year1 ={"\$(=Date(AddMonths(\$(v2), -2), 'YYYY-MM'))"} >} number)

• ###### Re: Set Analysis for Prior Month

ooopppss....my mistake....thanks a lot Sunny....if I want to show value for last 13 months then what would be the expression?

• ###### Re: Set Analysis for Prior Month

I think below would be the correct statement:

Count({<Month_Year1 ={">=\$(=Date(addmonths(\$(v2), -13), 'YYYY-MM')) <=\$(=Date(addmonths(\$(v2), -1),  'YYYY-MM'))"} >} number)

• ###### Re: Set Analysis for Prior Month

Yes this looks right to me.

• ###### Re: Set Analysis for Prior Month

Hey Sunny,

I am using below expression for rolling 13 month. But it is always showing for default month that is Max Month. If I change my month it is not working.

Count({\$<Month_Year1 ={">=\$(=Date(addmonths(\$(v2), -13), 'YYYY-MM')) <=\$(=Date(addmonths(\$(v2), -1),  'YYYY-MM'))"} >} number)

Could you please check my expression.

Thanks,

Sarif

• ###### Re: Set Analysis for Prior Month

You might have made selection in another date related field, have you? Add any date related field where you might have made selection:

Count({\$<Month_Year1 ={">=\$(=Date(addmonths(\$(v2), -13), 'YYYY-MM')) <=\$(=Date(addmonths(\$(v2), -1),  'YYYY-MM'))"}, Month, MonthID>} number)

• ###### Re: Set Analysis for Prior Month

yes Sunny .. this kind of issue was there. I rectified that. Now it is changing its value on the basis of selection. Now another issue is it is showing for all months instead of showing last 13 months.

I have one field called sys_created_on. I calculated month using below script:

Date( MonthStart( sys_created_on ), 'YYYY-MMM') as [Month_Year],

then

set v1= max(Month_Year);

Our filter we have [Month_Year] and dimension of the chart also the same.

Below is my expression:

Count({\$<Month_Year ={">=\$(=Date(addmonths(\$(v), -13), 'YYYY-MMM')) <=\$(=Date(addmonths(\$(v1), -1),  'YYYY-MMM'))"} >} number)

Thanks in advances for your help.

Thanks,

Sarif

• ###### Re: Set Analysis for Prior Month

Not sure if this is a typo, but you are using \$(v) instead of \$(v1) here?

Count({\$<Month_Year ={">=\$(=Date(AddMonths(\$(v), -13), 'YYYY-MMM'))<=\$(=Date(AddMonths(\$(v1), -1),  'YYYY-MMM'))"}>} number)

• ###### Re: Set Analysis for Prior Month

sorry that is typo... correct one is below:

Count({\$<Month_Year ={">=\$(=Date(addmonths(\$(v1), -13), 'YYYY-MMM')) <=\$(=Date(addmonths(\$(v1), -1),  'YYYY-MMM'))"} >} number)

• ###### Re: Set Analysis for Prior Month

I don't see anything wrong with the expression... may be try this without the dollar sign expansion:

Count({\$<Month_Year ={">=\$(=Date(AddMonths(v1, -13), 'YYYY-MMM'))<=\$(=Date(AddMonths(v1, -1),  'YYYY-MMM'))"}>} number)