3 Replies Latest reply: Jan 17, 2012 10:05 AM by Miguel Angel Baeyens de Arce RSS

    Set analysis issue in straight table

      Hi there,


      I have the following problem. I have a delivery which is linked to a shiftdate. This delivery is done at a site which has certain delivery hours. Now I want to show in a straight table which was the deliverywindow for that site on that shiftdate. Right now my set analysis looks as follows:



      only({<DeliveryWeekDay = {$(=WeekDayNum)}>} DeliveryStartTime)


      DeliveryWeekDay and DeliveryStartTime are specified in the deliveryhours table

      WeekDayNum is the weekdaynumber of the master calendar


      The problem with his equation is that it will evaluate WeekDayNum as a variable instead of using the ShiftDate Dimension to evaluate it. Is there a way so that it will take the DeliveryWeekDay of the ShiftDate specified as a dimension? Thanks!

        • Re: Set analysis issue in straight table
          Miguel Angel Baeyens de Arce



          Do you mean using ShiftDate week day instead of WeekDayNum? Then something like this might work


          Only({< DeliveryWeekDay = {"=Num(WeekDay(ShiftDate))"} >} DeliveryStartTime)


          Depending on how you have linked your tables this may make no sense at all, though. A few sample records would be great to better understand your model and what results do you expect.


          Hope that helps.



            • Re: Set analysis issue in straight table

              Hi Miguel,


              WeekDayNum would be fine to use. The problem is that deliveryweekday and ShiftDate are detached. The model is connected as shown below.


              ShiftDate - Delivery - SiteId - DeliveryHourWeekDay - DeliveryStartTime


              The set analysis still evauluates all the DeliveryWeekDay instead of 1 since it somehow doesn't uses the ShiftDate from the dimension but all available shiftdates. If I select a single shiftdate it works correct.


              Hope you understand the problem.

                • Re: Set analysis issue in straight table
                  Miguel Angel Baeyens de Arce



                  If it's detached, then it's unlikely to work. You may use the P() function to get all possible values, but that will return all values as well. Without knowing your model, I've run out of suggestions except for:


                  Only(DeliveryStartTime * (ShiftDate=DeliveryDate) * -1)


                  where DeliveryDate is the field in the same table that DeliveryStartTime that stores the date in the same format that ShiftDate.