2 Replies Latest reply: Jul 16, 2013 4:02 PM by Robert Greene RSS

    Find only 1 Row per order - Is it possible with set analysis ?

    Robert Greene

      Hello All, I hope someone can throw some light on this for me. Even if it's to tell me it can't be done. ;-)   (Always a challenge)

       

      Find and show HIGHEST value in a dimension within a date range.

      I would like to restrict the amount of rows shown on a straight table (Table 2) using set analysis to 1 row per order for a given number of scenario's

       

      1. Variables.  (not sure if all these are necessary, there may be an easier way of doing this)

       

      //If there is no date selected use the max date in the Date Island

      vReportingdate           =if(not ISnull(only([Date Island])),only([Date Island]),vMaxDate)

       

      vMaxDate                  =10/04/2013

      vLastUpdated             =if(only(%NoOfDaysSinceUpdate)=0,'$(vReportingDate)','<=$(vReportingDate)>$(vReportingDateFrom)')

      vLastUpdateLimit        =%NoOfDaysSinceUpdate

      vReportingDateFrom   =Date((vReportingDate - vLastUpdateLimit),'DD/MM/YYYY')

      vAmountLimit             =%Amount_Limit

       

      //////////////////////////////////////////////////////////////////////

      Scenario 1

      Default %NoOfDaysSinceUpdate = 0

      No Date Island value selected.

      No Bookmark

       

      When no date is selected in Date Island I want table '2. Set Expression' to show any orders that have been updated on that date.

      If there is and %AmountLimit selected then also select orders below that limit.

       

      The expression used is this and does what it needs to in the scenario.


      sum
      ({<
      [Update Date] = {'$(vLastUpdated)'},
      Amount = {'<=$(vAmountLimit)'}
      >}
      Amount)

       

      //////////////////////////////////////////////////////////////////////

      Scenario 2

      %NoOfDaysSinceUpdate = 30

      No Date Island value selected.

      BookMark 10/04/2013 - 30 Days

       

      Show the latest updated version to any orders that have been updated between the max date and the (max date - %NoOfDaysSinceUpdate)

      Order1 Version 6 Correct

      Order 3 Version 6 Correct

      Order 2 Version 3,4,5,6 INCORRECT, I get 4 rows because 4 versions have been updated between <=10/04/2013>11/03/2013

       

      //////////////////////////////////////////////////////////////////////

      Scenario 3

      %NoOfDaysSinceUpdate = 60

      20/03/2013 selected in Date Island

      BookMark 20/03/2013 - 60 Days

       

      Show the latest updated version to any orders that have been updated between the max date and the (max date - %NoOfDaysSinceUpdate)

      Order1 Version 5 Correct

      Order 3 Version 5 Correct

      Order 2 Version 1,2,3,4,5 INCORRECT, I get 5 rows because 5 versions have been updated between <=20/03/2013>19/01/2013

       

      //////////////////////////////////////////////////////////////////////

       

      These are just examples to prove the functionality works or not.

      The app is attached.

       

      I would be extremely grateful for any help or pointers in the right direction.

       

      Many thanks

      Rob