Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try this:
If(AmountExpression > 0, Sum(TOTAL <ProductDimension> [Hit Ratio]))
Hi Pekka,
are you able to upload an example QVW which illustrates the issue?
Andy
Try this:
If(AmountExpression > 0, Sum(TOTAL <ProductDimension> [Hit Ratio]))
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.
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
I will give it a try I'll let you know if it worked!
This solution is way better in terms of performance and simplicity than the one I proposed myself!