Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
))))
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)?
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?
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
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?
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?