SQL aggregation vs Projection function

[system]

Hi,

I have a issue with the object created in the Universe.

I have used Min() function in defining a measure object but the client had a concern on this saying it is causing inconsistency when used with other measure objects

 min(( round(sysdate -(table.Date_DT)) ))

Is there anyways so that i could provide a resolution on this.

The client says we are getting inconsistent results using this with other measure objects that have SUM() as the projection function at universe level.

We are on ORacle database.
Can we create the object in a different way to avoid the incosistency problems? Please advice.

---

Achilies (BOB member since 2008-12-04)

[system]

your definition of “inconsistency” is not clear to me. Can you elaborate the issue by providing sample data? What output do you get with and without using your object with other measure objects? That way it will be clear where exactly the problem is.

Thanks,
Raghvendra Deshpande

---

Raghvendra Deshpande (BOB member since 2008-05-26)

[system]

Thanks for the reply. Please find the details below.

#Mobject
=min(( round(sysdate -(table.Date_DT)) ))

For the above object, i have defined projection aggregation as SUM() and used SQL aggregation in the object defeinition as MIN().

The above object creates the below output:

ID–Dept–Amnt–#Mobject
12–012–1000–0
13–023-- 500–0

Now i created the same included other objects as below

ID–Hist–Dept–Amnt–#Mobject
12–001–012–1000–1
12–002–012–1000–0
13–001–012–500–1
13–002–012–500–0

When i remove the second column:

ID–Dept–Amnt–#Mobject
12–012–2000–0
13–023-- 1000–0

The [Amnt] object is summing because the projection property is Sum rather than Min:

This is causing inconsistency.

---

Achilies (BOB member since 2008-12-04)

[MarkP]

Why would you want to sum a minimum?

Set the projection of a minimum to minimum

[system]

Please, read my post about proper definition of measures including SQL aggregation as well as projection here: diff between Select SUM (Table. Field) & Select Table. F

---

Andreas (BOB member since 2002-06-20)

[system]

Thanks Mark,

Yes I have changed it to Minimum. But i am not clear what my client says.
He wants that to be fixed. But, though i change the SUM projection to MIN, the inconsistency does not go.

What exactly is meant i am still not clear.

---

Achilies (BOB member since 2008-12-04)

[system]

But i am not clear what my client says.

:?

But, though i change the SUM projection to MIN, the inconsistency does not go.

Please provide the outputs / examples you see now the projection is set to min.

---

Mak 1 (BOB member since 2005-01-06)

[system]

Thanks Mak.

The above given examples is what i can see even after i change the aggregation from SUM to MIN

---

Achilies (BOB member since 2008-12-04)

[system]

Depending on what dimensions you wish to calculate the min for? You may need a calculation context to display what you want.

When i remove the second column:

Quote:
ID–Dept–Amnt–#Mobject
12–012–2000–0
13–023-- 1000–0

The [Amnt] object is summing because the projection property is Sum rather than Min:

This is causing inconsistency.

You seem to be talking about something else here. The sum would be expected behaviour for amount, if it has a sum projection.

---

Mak 1 (BOB member since 2005-01-06)