Filter Condition on Dates

[Anonymous]

Hi

I need to have a condition in the script which gives total available hours , considering the tester whose decommision date is not greater than the date.

I have 4 tables :

3 dimension tables and 1 fact table

dim_date : has all possible dates in all formats

dim_tester : has the all the tester values , and its inception ( when the tester was bought) and decommsion(when the tester was decommsioned or removed)

dim_time : which has hour seg prime values , and the available hours for a day

Fct_tester_utilization : this table has the keys to link all the dimension tables and the logged hours (used hours) details.

i need to calculate available hours based on the inception date and decommision date.

A tester is considered to be online if its between inception and decommsion date, and available hours has to be calculated for all the testers online, if the tester is decommisioned then it has the date value of when its decommsioned if not it has the far value like 12/31/9999.

Please find the attached qvw for sample data and the datamodel.

[Anonymous]

Any help with this please ....

[pover]

You might be able to put something in the script if the reference date was fixed. For example you could flag all decommissioned testers as of today, but if the user is going to select the date then to put a flag for every possible reference date is irrealistic. The best solution might be replacing all expression with a

sum(if(decommissioned_date<dim_date,available_hours))

Otherwise, have you looked into using the intervalmatch() function in your script to link the dim_date to the dim_tester with fct_tester_utilization? You can link the table with both the Tester_Key and the range of dates TESTER_DECOMMISSION_DATE and TESTER_INCEPTION_DATE with DIM_DATE_KEY in the intervalmatch() and use an inner join between the tables to eliminate values outside the range.

One big question I have is, why is the tester still logging details in dates after it has been decommissioned?

Regards.

[Anonymous]

Hi Karl

Thank you for the reply ...i used the expression sum(if(decommissioned_date<dim_date,available_hours)) as suggested but i dont see any testers with decommision_date 12/31/9999 ,but those testers with 12/31/9999 are meant to be online, how do i include their available hours?.

And also i have a generel question , this report is actually a business objects report , is there a way where we convert BO reports to Qlikview , instead of doing qlikview reports from the scratch ? as BO has pleanty of derived table based on the prompt values its becoming its so tough to understand the concepts for me.

[pover]

Every day they make different tools to use with QlikView, but I haven't seen anything to convert a BO report to QlikView.

Sorry for the lapse in thinking, but the formula should be with a greater than sign:

sum(if(decommissioned_date>dim_date,available_hours))

Regards.

[Anonymous]

Hi

I have generated Available Hours with the above condition and loaded the data , but in the graph i have a calculation which matches if i do manually , but does not show in the graph , please look into the qvw i had attached.

Thanks

[pover]

Try the following formula because all tester are not counted in every hou.r segment.

Available_Hours * Count (total <Week> DISTINCT tester)*7

Regards