2 Replies Latest reply: Feb 20, 2013 8:13 AM by rikardherminge RSS

    Using set analysis to get a set of dates based on variables

      I have a problem that I have struggled with for too long. The basic problem is that I want to get the sum of sales between two different dates, based on variables. When I write my expression using two static dates, it works. Below it the code with static dates:

       

      Sum( { $ < Date= P ( { 1< OrderDateNum= { ">=38930<=38960" } > }  Date) >}  OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

       

       

      As you can see I use the numeric value for dates. In my load script I have created an extra field "OrderDateNum" for matching. Maybe I have made things more complex, but I have tried and googled everything. It all comes down to converting I think. Below it the expression that I have tried:

       

      Sum( {$ < Date= P ( { 1< OrderDateNum= { ">=$(_vMTD_start_date)<=$(_vChosenDate)" } > }  Date ) >}  OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

       

      I have the two variables _vChosenDate and _vMTD_start_date, and they are booth the numeric versions of dates. Apparently the upper limit variable "_vChosenDate" is used, but not the lower limit variable.

       

      I have also tried the following,

       

      Sum( { $ < Date= { ">=DATE($(_vMTD_start_date_NUM))<=DATE($(_vChosenDate_NUM))" } > }  OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

       

      Can anyone help me?

        • Re: Using set analysis to get a set of dates based on variables
          Gysbert Wassenaar

          You're probably right that the date format is the problem. Make sure your date fields and two variables contain real dates, not strings.

          Check if one of these works:

           

          Sum( { $ < Date= { ">=$(_vMTD_start_date)<=$(_vChosenDate)" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

           

          Sum( { $ < Date= { ">=$(=num($(_vMTD_start_date)))<=$(=num($(_vChosenDate)))" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

           

           

          Sum( { $ < OrderDateNum= { ">=$(_vMTD_start_date)<=$(_vChosenDate)" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )


          Sum( { $ < OrderDateNum= { ">=$(=num($(_vMTD_start_date)))<=$(=num($(_vChosenDate)))" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

           


            • Re: Using set analysis to get a set of dates based on variables

              Thanks, but your suggestions didn't work. I'm putting this problem on hold for today, so that I can look at it with new perspective tomorrow.

               

              What puzzles me is that when I user numbers in my expression (my first example) it works fine, and when I look at my variables in text-boxes they show the exact same numbers. But when I try to use the variables in the expression, it doesn't work. What I need is some nice way of looking at tables and varables and show the data types, to make sure that I have it right.