How to design a dimention about period-count quantity

[ggimmiwang]

Hi,
thanks a lot first for helping!

The situation is I want to design a dimention(measure) about the employees-quantity at our company(department)in one special period.I don't know how to design that!!
----
My data record
-----------
E_id, E_name,E_dept, E_arrival_work_date, E_departure_date
001,bob, mis,20030101,20030228
002,nick,hr,20030201,200300330
003,john,hr,20030201,200300330
---------------------------
The result I want to display is:

Qty |
in |
Company
| 1 3 2
------------------
200301 200302 200303

 

[DoubleD]

You'll need to generate a separate IQD containing a record for each month, an employee count, as well as any other necessary fields.

I am what I am based on the decisions I have made.

DoubleD

 

[ggimmiwang]

Thanks a lot for helping!!
Now, I'm trying to design such *.iqd and test it, but something is confused me--> the "employee count" change very sooner, is any way to calculate it in dynamic updating??

 

[DoubleD]

ggimmiwang,
I apologize but I don't understand your question.
Here's how I would envision the IQD:
Have a hotfile containing all the required Years and Months.
Then your IQD would be just two fields:
DateField and Employee Count
Employee Count could be calculated as follows:
Sum(If (DateField between (E_arrival_work_date) and (E_departure_date)) Then (1) Else (0))

I don't know that this is the exact solution, but it might put you on the right track.

I am what I am based on the decisions I have made.

DoubleD

 

[ggimmiwang]

Thanks Mr.DoubleD!!
I try to design the *.iqd you suggested, the result is not good because of "emoployee's work-time counting" !!
----------------------------------------
I have tried to use function "count",but the rusult-number just caculated only "a day" that employee's E_arrival_work_date time.(The correct number I want to caculate is "period-count" from row data.)I think the data stucture between the row data and the result data is different.

 

[ggimmiwang]

Thanks for reading!!
1.Where do I add this "Sum(If (DateField between (E_arrival_work_date) and (E_departure_date)) Then (1) Else (0))"

2. I have aready built a row data's iqd (which is compose of start-work & End-work field and etc..). where do I set the result-table(means the period-employee-count) and how to caculated and deploy the "count" dimension.

Thanks a lot!!!

 

[DoubleD]

Start-work and End-work can't be fields in your IQD for this to work.
Create a hotfile containing just one field, YearMonth.
The data in this field should look like this:
200301
200302
200303
etc.

Now write a report containing only two fields, include the field YearMonth (grouped), and a calculated field as shown below:
EmpCount: Sum(If (NumberToString(DateField) between (NumberToString(Year(E_arrival_work_date))+NumberToStringPadded(Month(E_arrival_work_date),2)) and NumberToString(Year(E_departure_date))+NumberToStringPadded(Month(E_departure_date),2)) Then (1) Else (0))

Now for each database record the calculated field will return a 1 or 0, then sum that for each HotFile record which are your list of months. Thus providing a count of employees by month.

I am what I am based on the decisions I have made.

DoubleD

 

[drlex]

DoubleD,
Excuse the butting-in, but I think the OP also wants the count to be related to the department. If such is so, you may therefore want to amend your report to account for such.

Happy Friday,
lex

soi la, soi carré

 

[DoubleD]

drlex,
I absolutely agree with you. You'll note in my earlier post:

You'll need to generate a separate IQD containing a record for each month, an employee count, as well as any other necessary fields.

I was just trying to take it a step at a time.

I am what I am based on the decisions I have made.

DoubleD

 

[drlex]

DoubleD,
Right you are. I was anticipating the reply of "I've done what you say but there's no breakdown by department".....

soi la, soi carré

 

[ggimmiwang]

hi,DoubleD:
I'm back unfortunately!! ^_^|||

1.I try to create the "datefield" hotfile you suggested, the way I try to is create the each month in "Excel" then I save it as *.csv, but I do not see it(that hotfiles)in Impromptu's catalog->Tables->hotfiles->browse when I want to add it to the right side.

Is the right way I use to follow your suggestion??
(Once I have used the hotfile(*.csv)is just in Transformer's data source)

Thanks a lot ^n !!!
--to be continue

 

[ggimmiwang]

I created a datefield table in database to solve the problem 1. above at first.(Because I want to trial the other in advance)

when I add that calculated field as your opinion above:
I got the 'cross product' error following :
---------
**** ERROR ****
This report will result in a 'cross product' query.

This User Profile prevents the use of 'cross product' reports.
-----------------
I'm trying to add the join the relation between the "employee" and "datefield" tables, but it seem to no data fields have the relation each other!

It's very sorry to disturb you again!!!

 

[DoubleD]

ggimmiwang,
You were on the right track when you got the Cross Product query error. If your Impromptu Administrator is not going to allow Cross Product queries, I think this is going to be much more difficult. Ask your Impromptu Administrator to change the catalog properties to allow you those rights. Joining to the table will not work unfortunately.

I am what I am based on the decisions I have made.

DoubleD

 

[ggimmiwang]

Thanks DoubleD
It's so amazing!
Now I'm trying to go the next steps(*.iqd -> transformer)!
thanks~~ !!