Set Analysis for Prior Month

[mhmmd_srf]

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

[sunny_talwar · Accepted solution]

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)

[mhmmd_srf]

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

[mhmmd_srf]

I think below would be the correct statement:

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

[sunny_talwar]

Yes this looks right to me.

[mhmmd_srf]

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

[sunny_talwar]

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)

[mhmmd_srf]

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

[sunny_talwar]

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)

[mhmmd_srf]

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

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