I need to analyze two dimension, please help.

[#1]

Hello,

This is my problem: I am currently working with a data base that assigns an user tittle to a employee depending on the amount of hours she or he reported in the time sheet. I have a table with all the emails and user tittle, but I don't know how to make an analysis that compares dimensions when repeated, the table looks like this:

month	email	usertittle	workinghours
Jan 2014	jcarson@something.com	developer	90
Feb 2014	jcarson@something.com	developer	10
Feb 2014	jcarson@something.com	manager	88
Mar 2014	jcarson@something.com	manager	93
Apr 2014	jcarson@something.com	manager	90

In this case the user jcarson was a developer in January, but in February he changed to manager, now the table has duplicates but I can not just load distinct because it would analyze witch user tittle the user really was in the month, in this example jcarson was a developer in February as well as a Manager, but given though he reported more hours (78 more) as a manager, I assign the manager tittle to this user.

I dont have a clue of the script I would need to write to make this kind of analysis every month, please help me

[Anonymous]

I would suggest you to keep on adding month on month data with out doing any changes to existing data. Also concatenate new data to existing data n join data based on user title.

You can use aggr function to arrive at the result n it would be work hrs by user title

[jolivares]

Try to post an excell sheet of what you are expecting.  Design a report with a simple columns.

[#4]

I might have not be to clear about what I am trying to accomplish. The idea is to set an analysis inside the data on the user dimension, so when every time this appear duplicate (the email dimension), it can  choose the user tittle with the most hours on its time sheet.

I am trying to do it by counting the email by months and then setting an if expression if the resulted count is bigger than one, thus this indicates there is a duplicate, and then with max over the hour dimension trying to get the biggest one of the duplicates, but it is not working.

[#5]

Done!

[#6]

Try this expression

=aggr(max(workinghours),email,month)

[Anonymous]

Hello Jesus,

please check the attached app.

the formula in pivot is as follows:

=if(sum(workinghours)<>max(total <month> aggr(sum(workinghours),month,usertittle)),null(),sum(workinghours))

How it works:

1. it puts NULL as value if the result is different than max result for given month.

Max: =max(total <month> aggr(sum(workinghours),month,usertittle))

2. Then in Presentation tab click Supress Zero values.

hope that explains.

rgds,

Artur

[#8]

Hi!

This works fine on the "dashboard", but I need to do it on the script, the reason is that this is just an step that leads to other calculations, where I need the exact number of "emails" per "usertittle", that is why I need to erase the ones that have the less working hours per month, when repeated.

Could you tell me how to do it in the script?

[Anonymous]

Hello Jesus,

please find the file attached where the calculation is all done in the script.

Hope that sorts you out.

regards,

Artur