problem with sum for timestampdiff

aPsikus

Hi,

I try to calculate Average Tenure (time of work) based on Vesting date and current date.

In theory, it should be sum for work time divided by number of employees, but I have stuck with sum part

Formula looks that way:

timestampdiff(sql_tsi_month,"Person"."Vesting Date",current_date)/12

I'm setting Aggregate rule SUM. But results are "strange". For two employees, where one has 14 years and second one has 7 years, as grand total it is displayed 182.

Any idea what I'm doing wrong?

Thomas Dodds

Did you try calculating the days in a physically mapped column then performing the aggregate?  I wonder if you are getting fetch/Aggr order issues.

aPsikus

Hi Thomas,

I'm not sure what do you mean...

Formula is created in OBIEE, directly in analysis, not in rpd. I don't thing that I can manipulate anything in that case.

Robert Angel

Try checking 'server complex aggregate' on the formula column in question.

Failing that, as Thomas correctly says your only recourse if that does not work is to calculate in the rpd using a physical formula - the problem is when OBIEE tries to aggregate, typically it will try to aggregate first and divide second - which is frequently not correct.

aPsikus

Server complex aggregate just repeats result for the first line.

I have tried to do it in rpd, but result was the same. Which means that I did something wrong or for timestampdiff you cant' sum it.

Robert Angel

Did you try in rpd as physical as opposed to logic column?

aPsikus

Do you mean that it should be created in Physical layer in table containing fields which I'm using in the formula?

Where formula should be created?

Robert Angel

Hi,

as below, tick the box for derived from physical mappings (as opposed to 'Derived from existing columns' which is a logical calculation) and then use the mapping to create the measure logic; -

aPsikus

Sorry to say, but it will not work.

I have column Age, which is created the way you have mentioned, and it is not summing correctly.

Don't have a clue, what OBIEE is doing.

Robert Angel

What aggregation rule do you have on that column, and what data type?

aPsikus

Data type is INT (visible on screenshot), no aggregation rule.

In the report I have set SUM.

I know what is going on: in DimPerson, there is more than one entry per employee id, in that case age or tenure is multiplied by number of entries.

Is it possible to add somehow to the formula that it should take the valid entry for current date?