6 Replies Latest reply: Jun 10, 2016 5:10 AM by Pekka Laaksonen

# summarize by one dimension in two dimension table

Hi,

I have two tables in my data model, one contains purchases from suppliers and contains sales offers to clients. My client buys products from suppliers and those products are then offered to clients. Between these two tables, only thing in common is the product number. So I cannot link purchases to sales offers and therefore tables are concatenated instead of joining them.

Now, I need to a report which has purchases and per supplier and product and sales offer success ratio per product. So my table looks something like this:

 Common field Only in purchases Only in purchases Only in purchases Only in sales offers Product Supplier Amount Qty Hit Ratio Product 1 Supplier A 100 10 Product 1 0 0 10 % Product 2 Supplier A 200 20 Product 2 0 0 20 % Product 3 Supplier A 300 30 Product 3 0 0 30 %

Now I'd like to get that Hit Ratio on same row as Amount and Qty. Problem is that Sales Offers is not present in Sales offers table and therefore I get two rows. I tried using Aggr function (=aggr(sum([Hit Ratio], Product) but that seemed to work only when I had Product selected.

Is there a way of getting that Hit Ratio on same row in chart?

• ###### Re: summarize by one dimension in two dimension table

Hi Pekka,

are you able to upload an example QVW which illustrates the issue?

Andy

• ###### Re: summarize by one dimension in two dimension table

Try this:

If(AmountExpression > 0, Sum(TOTAL <ProductDimension> [Hit Ratio]))

• ###### Re: summarize by one dimension in two dimension table

This solution is way better in terms of performance and simplicity than the one I proposed myself!

• ###### Re: summarize by one dimension in two dimension table

Hi,

Thanks for the replies. I'd rather not upload the .qvw since the data is my clients and I'm under quite strict NDA.

However, I think I got it working by applying AGGR function to Amount and Qty as well:

Amount: AGGR(Sum(Amount), Product, Supplier)

Qty: AGGR(Sum(Qty), Product, Supplier)

Hit Ratio: AGGR(sum(Missed)/sum(Offered), Product)

Solution provided by Sunny might work as well but I haven't tried it yet.

• ###### Re: summarize by one dimension in two dimension table

You should atleast give it a shot because Aggr can lower the performance. But I will leave for you to decide if you want to give it a shot or not.

Best,

Sunny

• ###### Re: summarize by one dimension in two dimension table

I will give it a try I'll let you know if it worked!