Aggregate function with condition depending of current row

building reports webintelligence
1

Hi,

I can’t find how to do something that seems quite basic, for the purpose of this topic, imagine that we have a webintelligence query returning several rows with 3 columns :

customer id / age / number of order

I would like, for exemple, write a formula that will calculate for each person the total number of orders of all customer with an age between customer age - 5 and customer age + 5

I don’t see how i can combine the same object of the query (the age) to be either the criteria (based on the current row), and the value to be tested (on rows being agregated).

Thanks for any clue on how doing this :slight_smile:

Sébastien

1 Like
2

It does seem basic, doesn’t it? I have not been able to come up with solution with a standard WebI universe query.

If you are able to create a free-hand SQL query you can do this pretty easily by joining the data back on itself based on the age range. I pulled some random data and dumped it in a temp table called #CustomerOrders.

SELECT
           a.[Customer Id]
         , a.[Customer Age]
         , a.[Number of Orders]
         , SUM (b.[Number of Orders]) AS [Number of Orders in Age Range]
FROM       #CustomerOrders a
INNER JOIN #CustomerOrders b ON b.[Customer Age] BETWEEN a.[Customer Age] - 5 AND a.[Customer Age] + 5
GROUP BY   a.[Customer Id]
         , a.[Customer Age]
         , a.[Number of Orders]
ORDER BY   a.[Customer Age];

Here is my sample data and result…

image
image546×335 58.1 KB

You do not need to order the data by Customer Age. I do so just for easier validation.

Now you cannot use a temp table in free-hand SQL, but you could put your query pulling your Customer Order data in the above query as a subquery in place of each instance of #CustomerOrders.

If you have control of your universe (or access to someone who does) I think you could probably add a derived table that creates this age range join.

Hopefully this will prompt someone else to chime in with simpler solution. If something here doesn’t make sense feel free to ask further questions.

Noel

3

@nscheaffer I think you’ve done the best that could be.

With Webi as a single pass tool, you were always going to have to think outside the box on this one.

4

Hi,

Thank you very much for thoses answers, i’m able to solve this issue using custom queries like Noel Scheaffer suggests, i’m also able to modify the universe.

But for me, the problem is that we are a large software provider, with many customers using our universe, and we can’t make adjustments in the universe for each complex customer request.

Our users are not able to write SQL neither…

I continue thinking about another solution… i’ll tell you here if i find it :wink:

Sébastien

5

Just a thought Noel - instead of a temp table, would a derived table within the universe work for your solution?

6

This what I suggested, right?

If you have control of your universe (or access to someone who does) I think you could probably add a derived table that creates this age range join.

:stuck_out_tongue_winking_eye:

7

Haha, missed that bit. I was too busy looking at the #CustomerOrders because it was in bold so deserved my attention. :stuck_out_tongue_winking_eye:

Definitely worth trying though, I agree.