1 Reply Latest reply: Jul 2, 2012 12:47 PM by Peyman Azimabadi RSS

    Running Selection on invoice date based upon dimension

      Hi Guys,


      I am stuck in an expression where I need to calculate the sum of on-demand quantity in a table with InvoiceAltYearMonth(Year-Month) as a dimension. For every year month, I need to look at a period which falls between the last day of that year month and a date 'OnDemandCode' weeks from it.


      Chart Dimension: InvoiceAltYearMonth(Year-Month)


      Start Date (based upon 'Go back X weeks' selection) (works fine):

      =Date(Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)-7*OnDemandCode, DateFormat)


      Last Day of Every month (works fine): 

      =Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)


      In the attached file, you can see that the start dates and end dates are found accurately based upon the InvoiceAltYearMonth dimension. However, I am unable to calculate the sum of on demand quantity between Start Date (based upon 'Go back X weeks' selection) and Last Day of Every Month


      PS: The start date is dynamic and is based upon the selection. Therefore, it's not necessary that the start date will always fall within the month in the dimension.


      I have tried two approaches:

      1. Create variables and write expression


      vStartDate:          =Date(Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)-7*OnDemandCode, DateFormat)

      vEndDate:           =Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)

      Final Expression Approach 1:      (Sum({<InvoiceDate={">=$(#vStartDate)<=$(#vEndDate)"}>} OnDemandQuantity))


      Through this approach, the expression is calculated accurately only between 2/18/2012 and 2/25/2012. This happens because the variables in my set analysis are calculated only once, and not for every dimension which I actually expect to happen !



      2. Write an expression by explicitly defining the start date and end date in the set analysis:


      Final Expression Approach 2: (Sum({<InvoiceDate={">=$(#=Date(Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)-7*OnDemandCode, DateFormat))<=$(#=Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate))"}>} OnDemandQuantity))


      This approach gives all zeros.


      Can anyone please help me in finding a solution to this problem ? The qvw and xlsx files are attached.