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

       

      Thanks,

      Bharat

        • Re: Need year start
          Stefan Wühl

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

           

          LOAD

               S5_Month2,

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

               ...

           

           

          Then:

          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.