13 Replies Latest reply on Aug 28, 2018 11:45 AM by Thomas Dodds

problem with sum for timestampdiff

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?

• 1. Re: problem with sum for timestampdiff

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.

• 2. Re: problem with sum for timestampdiff

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.

• 3. Re: problem with sum for timestampdiff

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.

• 4. Re: problem with sum for timestampdiff

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.

• 5. Re: problem with sum for timestampdiff

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

• 6. Re: problem with sum for timestampdiff

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?

• 7. Re: problem with sum for timestampdiff

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; - • 9. Re: problem with sum for timestampdiff

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

• 10. Re: problem with sum for timestampdiff

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?

• 11. Re: problem with sum for timestampdiff

Sure; -

case when 'WhateverConditionYouNeedHereIsTrue' then your FormulaHere else 0 end

Make the aggregation on it sum.

• 12. Re: problem with sum for timestampdiff

Also, if Dim Person has more than one person per person id then that is the least of your problems.

You need some kind of SCD functionality to make your employee 1:1 in all time periods, unless you are saying that the result you posted previously would work if you had made it; -

Period / Employee / Age??

• 13. Re: problem with sum for timestampdiff

This is looking more and more like a star modeling issue than a simple formula with a fetch/aggr ordering issue.