Bubbeling data

Here's an interesting problem I ran into last week:


Date                Code    Emplid Name of the Employee
2012/12/01 --  DTA -- 123 --  John
2010/12/10 -- XFR -- 123 -- John
2012/12/05 -- DTA -- 456 -- Bert
2012/05/08 -- DTA -- 567 -- Pascal
2000/06/04 -- DTA -- 567 -- Pascal
2000/05/01 -- XFR -- 567 -- Pascal



Right. My boss asked me, in the above list, to show him the transfers for people that got transferred, and for everyone else, he needed their most recent situation.

The tricky part is : you can't filter on a code and you can't filter on a date.

Part of the solution is of course to be found in the article where I showed you how to get the two most recent items. That is, we're going to turn our date into a number :

=(year([Date])*10000)+(monthnumberinyear([Date])*100) + daynumberinmonth([Date])

First, we need to create a break on Emplid of course.

CalcDate        Date             Code    Emplid Name of the Employee
20121201 -- 2012/12/01 --  DTA -- 123 --  John
20101210 -- 2010/12/10 -- XFR -- 123 -- John
20121205 -- 2012/12/05 -- DTA -- 456 -- Bert
20120508 -- 2012/05/08 -- DTA -- 567 -- Pascal
20000604 -- 2000/06/04 -- DTA -- 567 -- Pascal
20000501 -- 2000/05/01 -- XFR -- 567 -- Pascal


And then, we can create a rank on CalcDate and get our most recent items. Easy. BUT. There was an additional requirement. If the employee had a transfer, we need that person's most recent transfer.

Easy as pie :

=CalcDate * if([Code]="XFR") then (1000) else (1)

Of course, this messes up the date-look of Calcdate a bit, but it gets the job done. When you rank on it, you get :


CalcDate            Date             Code    Emplid Name of the Employee
20101210000 -- 2010/12/10 -- XFR -- 123 -- John
20121205       -- 2012/12/05 -- DTA -- 456 -- Bert
20000501000 -- 2000/05/01 -- XFR -- 567 -- Pascal

Mission accomplished :)

Cheers

Peter

Forecast formula in Business Objects

I noticed someone searching for the following : target sales revenue formula

Obviously, it's rather simplistic, but here's a formula to try calculate a future number, when you already have some numbers from the past.

Let's say we had numbers for 4 quarters and 2 years.

2010 Q1 1000
2010 Q2 500
2010 Q3 900
2010 Q4 800

2011 Q1 1250
2011 Q2 800
2011 Q3 800
2011 Q4 700

and we want to figure out, if that trend continues, what the numbers for 2012 will look like. Here's how:

=(sum([Sales] where ([Year]="2010"))  * 0.25) +(sum([Sales] where ([Year]="2011"))  * 0.75)

For Q1 2012, this would give : 250 + 937.5 = 1187.5

Of course, you could add a fraction, based on your own business knowledge, but it's a start.

Random selection of 300 in Business Objects

Now that was cool.

My boss said. I need you to make me a Business Objects Query. The KUL University asked us to provide a random selection of employees.

They need the selection to have the right percentage of Age groups in it.
The group should have the right amount of Men and Women in it
It should represent our departments and job functions proportionately.

You'll probably need two days to set this up.

I don't know much about statistics, but I do know that a random selection of 300 out of 9000, if the selection is truly random, I don't have to bother with the rest of that stuff, the groups will be properly represented.

So, I went ahead and made a query that gave me the Company's population.

9000 people
47% Women
53% Men

Then, I applied the following little trick -- and this is the purpose of this very article:

In Business Objects Query panel, I chose to write custom SQL and I then put this little piece of code there: at the very bottom of the SQL.

ORDER BY dbms_random.value (only for Oracle)

This will randomize the list.

Next, in the query's options, I set the option to only return 300 results.

15 minutes after my boss told me to start doing a 2-day job, I finished it.

Of course, I made the same percentage calculations and obviously, they returned the same results as the master list.

If you need to do this in other DBMS systems, the code is:



MySQL:

ORDER BY RAND()
LIMIT 1


Microsoft SQL:
ORDER BY NEWID()

Enjoy