year selection

[#1]

If We select year, chart should display from previous month to preceeding 12 months.

I have written the below expression but I am getting issue, like If I select 2017 year it is taking from prev month upto Jan 2017.

But I need to display upto June 2016.

Can any one suggest me this?

[#2]

If(Getselectedcount(year)>0,

(if(NumOfFailures=0,

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vPrevMonthYear)'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>}  AssetMiles)

/

Sum({<CalendarMonth={'$(vPrevMonthYear)'},EquipmentType={'GP-40MC'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures),

Sum( {<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} AssetMiles)

/

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures)

))),

(if(NumOfFailures=0,

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vPrevMonthYear)'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>}  AssetMiles)

/

Sum({<CalendarMonth={'$(vPrevMonthYear)'},EquipmentType={'GP-40MC'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures),

Sum( {<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} AssetMiles)

/

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures)

))))

[sunny_talwar]

What is your expression? Did you remember to include Year field in your set analysis so that you can  exclude the selection in the Year field (the field where you are making selection)?

[sunny_talwar]

Why are you using set analysis on CalendarMonth field twice?

You know that the second one will not work, right? I don't see the reason to do this?

And what is NumOfFailures? Field? or Variable? Also, if it is a field, is this from a Island Table?

[#5]

One is for month but I tried for year I dint get any result to show the scenario I copied

My only intention, When I am selecting Year from that year, month should display from previous month to preceding 12 months and if I select month that is coming properly.

Numoffailures is field that is not coming from the isolated table. that field is coming from the fact table which is integrated from different transaction tables

[sunny_talwar]

It might be easier to help if you can share a sample where you might be doing this... Would you be able to share a sample?

[#7]

Hi Sunny,

Thanks for your response, please find the below sample data

AssetMiles	NumOfFailures	CalendarKey
35.14	0	20150413
35.14	0	20140319
35.56	0	20150211
35.56	0	20140823
43.6	0	20140111
49.55	0	20150529
62.8	0	20140718
50.94	0	20140629
50.94	0	20140622
62.7	0	20140204
70.28	0	20141212
70.28	0	20140328
70.28	0	20140228
71.12	0	20140727
82	0	20140403
88.2	0	20140911
99.1	0	20141114
99.1	0	20140509
56.93	0	20150520
84.15	0	20150528
87.14	0	20140620
95.7	0	20150310
101.9	0	20150218
115.76	0	20150316
123.34	0	20140327
125.6	0	20140410
130.8	0	20150216
131.8	0	20150209
104.6	0	20150311
105.88	0	20150401
106.4	0	20150202
106.48	0	20140502
114.53	0	20150527
119.68	0	20140131
121.2	0	20140529
140.98	0	20140905
141.02	0	20141211
141.02	0	20141210
141.44	0	20140226
141.44	0	20140123
142.24	0	20140202
148.8	0	20140322
158.9	0	20140324
164	0	20141022
164	0	20141106
164	0	20141016

I have one more doubt, we have filter Month(JAN,FEB.......), Year(2014,2015..).  If we select Aug value and year 2015 it should display from from July 2015 to Jun 2014. is it possible?