topic Re: Set analysis issue in straight table in QlikView

Set analysis issue in straight table

Tue, 17 Jan 2012 13:58:03 GMT

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 — Tue, 17 Jan 2012 14:29:00 GMT

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

Re: Set analysis issue in straight table

Tue, 17 Jan 2012 14:42:31 GMT

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 — Tue, 17 Jan 2012 15:05:11 GMT

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