topic Linking fields on set analysis in QlikView

Linking fields on set analysis

vesiletaskiran — Tue, 22 Jan 2019 15:22:58 GMT

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.

Re: Linking fields on set analysis

sunny_talwar — Tue, 22 Jan 2019 15:33:08 GMT

May be join the two tables using a Link Table

Re: Linking fields on set analysis

dplr-rn — Tue, 22 Jan 2019 15:35:43 GMT

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

Re: Linking fields on set analysis

jwjackso — Tue, 22 Jan 2019 15:37:55 GMT

Should the formula be

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

to get minutes/ticket.

Re: Linking fields on set analysis

vesiletaskiran — Tue, 22 Jan 2019 15:45:20 GMT

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)

Re: Linking fields on set analysis

sunny_talwar — Tue, 22 Jan 2019 15:50:16 GMT

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.