6 Replies Latest reply: Jun 30, 2017 12:36 PM by kumarsuresh lulu RSS

    year selection

    kumarsuresh lulu

      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?

        • Re: year selection
          kumarsuresh lulu

          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)

          ))))

            • Re: year selection
              Sunny Talwar

              Why are you using set analysis on CalendarMonth field twice?

               

              Capture.PNG

               

              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?

            • Re: year selection
              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)?

              • Re: year selection
                kumarsuresh lulu

                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

                • Re: year selection
                  kumarsuresh lulu

                  Hi Sunny,

                   

                  Thanks for your response, please find the below sample data

                     

                  AssetMilesNumOfFailuresCalendarKey
                  35.14020150413
                  35.14020140319
                  35.56020150211
                  35.56020140823
                  43.6020140111
                  49.55020150529
                  62.8020140718
                  50.94020140629
                  50.94020140622
                  62.7020140204
                  70.28020141212
                  70.28020140328
                  70.28020140228
                  71.12020140727
                  82020140403
                  88.2020140911
                  99.1020141114
                  99.1020140509
                  56.93020150520
                  84.15020150528
                  87.14020140620
                  95.7020150310
                  101.9020150218
                  115.76020150316
                  123.34020140327
                  125.6020140410
                  130.8020150216
                  131.8020150209
                  104.6020150311
                  105.88020150401
                  106.4020150202
                  106.48020140502
                  114.53020150527
                  119.68020140131
                  121.2020140529
                  140.98020140905
                  141.02020141211
                  141.02020141210
                  141.44020140226
                  141.44020140123
                  142.24020140202
                  148.8020140322
                  158.9020140324
                  164020141022
                  164020141106
                  164020141016

                   

                  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?