3 Replies Latest reply: Jul 18, 2016 3:42 AM by naveen kumar RSS

    sum of Date Range

    naveen kumar

      Hello All,

      i am bit confuse and surprise that if i am able to get two diff value (min and max) separately ,y not i am getting them when i merge them ...

      what i mean is

      Varibles:

      vMindate= 3 sep

      vMaxdate=  =Date(Max({Group2}Date),'DD MMM')

       

      i am getting value for the below two expression:

      1)textbox=

      =Num(sum({<Date={"$(vMindate)"},diss={10}, work={0}>} value)/

      sum({<Date={"$(vMindate)"},diss={10}>} value),'##0%')


      2)textbox=

      =Num(sum({<Date={"$(vMaxdate)"},diss={10}, work={0}>} value)/

      sum({<Date={"$(vMaxdate)"},diss={10}>} value),'##0%')

       

      but when i tried to merge them like below exp

      3)textbox=

      =Num(sum({<Date={">=$(vMindate)<=$(vMaxdate)"},diss={10}, work={0}>} value)/

      sum({<Date={">=$(vMindate)<=$(vMaxdate)"},diss={10}>} value),'##0%')

       

      i am getting   -    as o/p

       

      my date format is like this : 

      date

      3 jan

      4 jan

      .

      .

      .

      10 jan

      11 jan

      .

      .

      some one earlier said that by date is in text format,is that the issues,if so can anyone help me how to resolve this plz

        • Re: sum of Date Range
          Jonathan Dienst

          >>sum({<Date={"$(vMaxdate)"},....

           

          This expression will expect Date to also have the format 'DD MMM', and both [Date] and the variables must be numerical date values, not text, for the range comparison to work correctly. I assume that DD MMM is not the default date format for you system.

           

          vMaxDate should be a date value, but vMinDate will be a text value. Rather define it like this:

          Let vMindate = Date(Date#('3 sep' & Year(Today()), 'DD MMMyyyy'), 'DD MMM');

           

          and make sure that Date is a numeric date value.

            • Re: sum of Date Range
              naveen kumar

              can i use this for

              vMindate =Date(Min({Group2}Date),'D MMM') (probelm with this exp is,suppose  if i select 16 sep then it is showing 16 sep as min value which is suppose to be 3 jan)

              i am getting Null as o/p when i use ur exp in textbox

              Date(Date#('3 sep' & Year(Today()), 'DD MMMyyyy'), 'DD MMM');


              • Re: sum of Date Range
                naveen kumar

                can i use this for

                vMindate =Date(Min({Group2}Date),'D MMM') (probelm with this exp is,suppose  if i select 16 jan then it is showing 16 jan as min value which is suppose to be 3 jan)

                i am getting Null as o/p when i use ur exp in textbox

                Date(Date#('3 sep' & Year(Today()), 'DD MMMyyyy'), 'DD MMM');