Linking fields on set analysis

[vesiletaskiran]

I have 2 qvds.
1-Daily Employee list and their total working time
TEAM,Employee, DATE, WORKING_MINUTES(8 HOURS A DAY)

2- Daily tickets closed by each team.
Team, TICKET, DATE, TOTAL_TICKETS

I need to find how much effort each team spent per ticket everyday.

When i try as below it gives same value for every team.

COUNT({$<Team=P(TEAM)>} TICKET_ID)/COUNT({$<Team=P(TEAM)>} WORKING_MINUTES)

I dont want to create syntetic keys or aggregate data on script bcz of performance issues.
What would be the appropriate/efficient way to solve such case?

Thanks.

[sunny_talwar · Accepted solution]

May be join the two tables using a Link Table

[dplr-rn]

No 100% sure what you mean. but from what i understand you need to know on a daily basis effort spent on each ticket.

Isnt that simply creating a table with date and team as the dimensions with Sum(Numof Tickets)/Sum(Working hours)

please elaborate if i misunderstood

[jwjackso]

Should the formula be

Sum({$<Team=P(TEAM)>} WORKING_MINUTES)/ COUNT({$<Team=P(TEAM)>} TICKET_ID)

to get minutes/ticket.

[vesiletaskiran]

Sorry you are right. but still trying to match this way doesnt work.On ticket qvd i have 2.000.000 records thats why i am trying to avoid syntetic keys , link table or aggregating on script. 

<Team=P(TEAM)

[sunny_talwar]

By avoiding things to be done at the script level will give you/your users headaches on the front end... but if that is acceptable... then try this

Count(If(Team = TEAM, TICKET_ID))/
Count(If(Team = TEAM, WORKING_MINUTES))

Once again, this will be a very slow performing expression, but if you are not fixing your issue in the script this is the best you can do... set analysis won't work.