Problem of aggregation

[Former Member]

Dear all,

BO Version : XI 4.0

I have a big problem of aggregation in WebI and I don't understand.

To simply, I have 1 dimension DATE and 3 indicators ( type sum ) QTY_1, QTY_2 , AMOUNT

In the request analysis window of WebI, I see the first problem :

1) There is a distinct on all the result of my request and I never ask it. Where can I change it ?

2) Per each date, the request repeats QTY_1 and QTY_2 per AMOUNT. Why not, if it is only for the display of the request select but, in my report, the aggregation sum is totally wrong because :

I need the distinct QTY_1 and QTY_2 and the sum( AMOUNT)

Why do I need to do it ??

In my understanding, for each date, BO have to do the correct aggregation sum by itself !? Else I don't understand the interest to use indicators

Thank you to help me.

Best regards,

Mike

[Former Member · Accepted solution]

In this case what you can do is count the dates and divide this to all your measures. like

=[QTY1]/Count([DATE];All)

=[QTY2]/Count([DATE];All)

=[AMOUNT]/Count([DATE];All)

[former_member184594]

First of all, what do you mean by "there is a distinct on all the result"? What kind of data do you want to see in your report? If it is possible, please attach a screen shot.

Secondly, there could be so many reasons you are getting wrong results in your report. You might be getting partial results, your universe might have problems etc.

[Former Member]

Hi Zahid,

I mean that the query start by SELECT DISTINCT but I have changed it to replace the DISTINCT_VALUES with a group by in the file parameters of my universe.

And for the moment, I search to aggregate correctly the datas with the group by condition and some additionnal dimensions if needed.

[Former Member]

I will suggest to remove the retrieve duplicate rows checkbox in the query properties in query panel..

Check the screenshot

[Former Member]

At the begining, I have already removed this option. It was a part of the problem but not only.

Now, I think that I have to add some dimensions to have the correct result.

[Former Member]

Change the aggreagtion at universe to be sum If not done

QTY_1, QTY_2 , AMOUNT

And in the SQL Provide sum ( QTY_1) , Sum(QTY_2) , Sum (Amount) .

Check if your date have timestamp attached to date , If there is timestamp then they will be counted as Separate rows :