Set analysis issue in straight table

[#1]

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!

[Miguel_Angel_Baeyens]

Hi,

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.

Miguel

[#3]

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.

[Miguel_Angel_Baeyens]

Ok,

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.

Regards.

Miguel