12 Replies Latest reply: Sep 5, 2016 3:03 PM by Bharat Kishore RSS

    Need year start

    Bharat Kishore

      Hi All,


      I am having a field called month and in that the data like Jan-15,Feb-15.....May-16.


      Now first thing i needed is I need to get Jan-16..If  i select May-16 i am getting as Jan-16 by writing the following expression


      YearStart(S5_Month2) and for monthend i have given like this MonthEnd(S5_Month2)..


      Now i need to write an expression like sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='5/1/2016',([S5_Leads])))) without hard coding..


      Kindly suggest




        • Re: Need year start
          Stefan Wühl

          Convert your S5_Month2 to a date file using Date#() function, like




               Monthstart(Date#(S5_Month2,'MMM-YY')) as S5_Month2_Date,





          Sum(If( S5_Month2_Date >= Yearstart(Today()) and S5_Month2_Date <= /*how you derived '5/1/2016' */ , [S5_Leads]))

            • Re: Need year start
              Bharat Kishore

              Thanks a lot stefan.. i have almost done but i needed slight help when i selecting the filter of May-16 by giving the following expression as :


              YearStart(S5_Month2) then i am getting as Jan-16 but can you pls tell me how can i achieve Jan-15..


              Because by hard coding i have written the following expression:


              =(sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='5/1/2016',([S5_Leads]))))-sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='5/1/2015',([S5_Leads])))))/sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='5/1/2015',([S5_Leads]))))


              Now my requirement is if i select filter of month the values should display according the filter i selected in month.


              Suppose if i select Apr in the filter then the expression should be


              =(sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='4/1/2016',([S5_Leads]))))-sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='4/1/2015',([S5_Leads])))))/sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='4/1/2015',([S5_Leads]))))

              I explained you by hard coding but now i want to remove hard coding.

              Kindly suggest..


              Please let me know if you require anything more.