summarize by one dimension in two dimension table

[#1]

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?

[awhitfield]

Hi Pekka,

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

Andy

[sunny_talwar · Accepted solution]

Try this:

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

[#4]

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.

[sunny_talwar]

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

[#6]

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

[#7]

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