Qlikview expression problem..need some help

[#1]

Hello All,

I have a table which has the following type of structure

Resource	Project Start Date	Project End Date
X	1-Jan-11	30-Jan-11
X	1-Mar-11	30-Apr-11
B	15-Apr-11	30-Dec-11
C	1-Feb-11	30-Apr-11
C	10-Nov-11	30-Dec-11
D	1-Nov-10	30-Apr-11
E	15-Nov-11	30-Dec-11

I have two calandar where i can select two dates...let suppose from 01-Jan-2011 to 30-Jul-2011

Now I want to calculate All the days that a resource is not busy in the selected timeframe. If a resource project start date is greater than the selected time span, and he has no projects in the selected timeframe, this means he is 100 percent available in this time.

How can i calculate this ?  I think Set analysis is going to be used in this one, but i am not much familier with set analysis

Arif

[flipside]

Hi Arif,

The attached looks like it does the trick, but I don't think it would handle correctly if a resource was on overlapping projects - more logic would be needed to handle that scenario.

flipside

[#3]

Arif,

Have a look at IntervalMatch which may help.

Regards,

Gordon

[#4]

Hello Flipside,

I have tried it to do it the way you have shown me. Everything is working correctly except the last column which is the on project day. It is working correctly on the example you I had given. However, on real data, the last column (On project days) is not being calculated correctly. Can you please tell me what could be wrong with it. It is calculating more days than the project days.

As shown in the image, the on project days for the first resource should be 45+134, but it is showing 760

[flipside]

Hi Arif,

I've fed your new dates into my copy and it works (think you've used date range 01/01/11 to 20/07/11), so my initial thoughts are that there could be a problem with local date settings (I'm using UK format DD/MM/YYYY for the data AND computer region settings). 

I have noticed that on my copy the Project sum is always top line per resource, whereas your screenshot shows variation.

flipside

EDIT:

Although not linked to your value errors, I have since noticed that my calculations were very slightly out because when subtracting two dates it will leave you one day short for the purpose of this problem.  You will need to add 1 day in the expressions where necessary as you want to include BOTH analysis dates.

Message was edited by: flipside

[#6]

Hello Flipside,

I am using these settings.

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='ma;tue;wed;thu;fri;sat;sun';

Also there is something really strange here

I have three dimensions .... Resource, Start Date and End Date......now the combination of all these three dimension form a different row. There is no repitition for the same row. That means that The expression of End Date - Start Date .......and sum(End Date - Start Date) should give the same value.

I have checked it on the sample data on which you had provided the solution. End Date - Start Date and sum(End date - Start Date) gives the same value. However, on real data, the sum gives a different value. Do you think Sum should give a different value ?

I was checking why the solution is not working on real data, and then i observed this strange thing. How can i check which columns are being grouped by, when i am taking the sum in the expression?

I have even checked the format and everything seems to be working fine

[flipside]

Hi Arif,

Unfortunately I just cannot replicate your problem.  The Sum() issue makes no difference for me.  I do see your data date format is displaying DD-MM-YYYY, but your QV DateFormat is set to DD.MM.YYYY.  Perhaps this is affecting the aggr expression.

I've also attached an alternative way of doing all this by creating a separate table in the script.  The Calendar_Master table is not necessary but allows the Gannt-like chart to be split into date periods.

Hope this helps,

flipside

EDIT:

Arif, I've noticed the other post you made (http://qlikcommunity.qliktech.com/message/159615#159615)  and the blank Resource lines in the screenshots suggest duplication is occurring when aggregating on Resource (chriscammers has explained it better than I can!). I think if you get your data structure right, both my solutions should work.

Message was edited by: flipside