1 Reply Latest reply: Mar 7, 2013 12:41 AM by Nirav Bhimani RSS

    How to get YTD , MTD values in a chart on selecting corrsponding YTD , MTD in a list box?

      Hi guys,

       

      I need to display my SALES and BUDGET data in a chart. The requirement is to get YTD data, when YTD is selected, MTD when MTD is selected I have put these in a LIST BOX and displayed them as LED. I am trying to use

      // This is put as respective flags in the master date table:
      
      inyeartodate(DateString, today(),0,4 ) * -1 AS YTD,
      InMonthToDate(DateString,today(),0)*-1 AS MTD
      
      
      
      // Sales and Budget Expression used in the chart to display data:
      
      
      SALES:  sum({<BudgetIndicator=>}(Value)/denoname)
      BUDGET:   sum(BudgetValue)/denoname
      
      
      
      // INLINE table for creating LED list box:
      
      PointInTime:
      LOAD * Inline [
      PointinTime
      YTD,
      MTD
      ];
      
      
      
      
      
      
      

       

      I am stuck at how to link my LED LIST box with this, so as to when I cllick on YTD or MTD on list box it, would show me the corresponding data?

      I know that the FLAGS need to go into the expression somehow, but not able ot figure it out completely. Plase help!!

        • Re: How to get YTD , MTD values in a chart on selecting corrsponding YTD , MTD in a list box?
          Nirav Bhimani

          Hi,

           

          Following are the expression

           

          For Year to Year Sales: e.g. 1/1/2011 to 31/12/2011

           

          (sum({<CalendarDate = {">=$(=num(Yearstart(max(CalendarDate))))<=$(=max(CalendarDate))"},CalendarMonthName=,CalendarQuarter=,Week=>}Sales))

           

           

          For Till date: e.g. 1/1/2007 to  7-03-2013

          In Script  QuarterStart(CalendarDate) as QuaterStartDate,

          In Variable vMaxDate= (Today())

                                             

          (sum(
          {<
          CalendarDate = {">=$(= Date (
          min({<CalendarWeekOfYear=, FinancialQuarter=, CalendarMonthName=>} QuaterStartDate )))<=$(vMaxDate))" }
          , CalendarWeekOfYear=, FinancialQuarter=, CalendarMonthName=
          >}
          Sales))

           

          For MTD :

          sum({<CalendarDate = {">=$(=num(Monthstart(max(CalendarDate))))<=$(=num(max(CalendarDate))) "},CalendarWeekOfYear=,CalendarQuarter=,CalendarMonthName=,CalendarYear=>}Sales)

           

          Regards,

          Nirav Bhimani