topic Re: summarize by one dimension in two dimension table in QlikView

summarize by one dimension in two dimension table

Wed, 01 Jun 2016 05:33:10 GMT

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

awhitfield — Wed, 01 Jun 2016 09:16:39 GMT

Hi Pekka,

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

Andy

Re: summarize by one dimension in two dimension table

sunny_talwar — Wed, 01 Jun 2016 10:03:12 GMT

Try this:

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

Re: summarize by one dimension in two dimension table

Wed, 01 Jun 2016 10:24:21 GMT

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

sunny_talwar — Wed, 01 Jun 2016 11:15:39 GMT

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

Wed, 01 Jun 2016 11:17:33 GMT

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

Re: summarize by one dimension in two dimension table

Fri, 10 Jun 2016 09:10:32 GMT

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