1 Reply Latest reply: Oct 27, 2011 8:41 AM by Roland Kunle RSS

    Set analysis with Dates

    Partha Patra

      Hi,

       

      I have a requirement whhere I need to check how many IDs have their DATE_A in the same range as my  reporting Date.

       

      I am trying a expression in a bar chart where:

      Dimension=MONTH

      Expression= =COUNT( {<DATE_A={"<=$(MAN_DATE)"},DATE_A={">=$(MIN_DATE)"},YEAR={"2011"}>} ID)

       

      But this expression is not grouping things by Month, as it would be in a bar chart. My MIN DATE AND MAX DATE are calculated columns in t my calender  table.

       

      MIN_DATE= ADDMONTHs(REPORTDATE,-3)

      MAX_DATE=REPORTDATE

       

      Any help is appreciated.

       

      Thanks

       

        • Set analysis with Dates

          Hello parpatra,

           

          two things to mention:

          your var MIN_DATE includes an expression (Addmonth ...). Due to this you have to use (in addition to $()) the $(=) - operator within your chartexpression. This would then look like:

           

          COUNT( {< DATE_A={"<=$(REPORTDATE)"},DATE_A={ ">=$(=$(MIN_DATE))"},YEAR={"2011"}>} Val)

           

          Second Note:

          My var MIN_DATE looks like:

          ADDMONTHs( $(REPORTDATE),-3)

          and

          REPORTDATE includes a valid (german) date:

          '28.10.2011'

           

          HtH

          Roland