Set Expression Help - Finding Best Pricing for a lot of product for one company?

[Noor51]

I been working on how to get the best price from a table in Qlik sense and struggling.  I want to pull the best price from the each price column which is also a expression and only display the lowest price for each product.

This is the table: The set exprssion that I using for best price is "aggr(min({<Vendor={'Vendor A'}>} Price) / Sum(Qty), [Product Code])"

Product Code	Price	Qty	Each Price (Sum(Price)/sum(Qty))	Best Price	Vendor
A	$115.54	10	$5.78	-	Vendor A
A	$173.26	20	$4.33	-	VendorB
A	$70.90	24	$2.95	-	Vendor C
A	$189.35	24	$7.89	-	VendorD
B	$233.22	10	$23.32	-	Vendor A
B	$124.89	10	$12.49	-	Vendor C
B	$186.60	10	$9.33	-	VendorD
B	$305.98	10	$15.30	-	Vendor G
C	$39.82	1	$39.82	-	VendorA
C	$62.71	1	$62.71	-	Vendor A
C	$54.89	1	$54.89	-	VendorB
C	$85.43	1	$85.43	-	Vendor C

But I want the table to look like this.

Product Code	Price	Qty	Each Price (Sum(Price)/sum(Qty))	Best Price	Vendor
A	$115.54	10	$5.78	$5.78	Vendor A
A	$173.26	20	$4.33	-	VendorB
A	$70.90	24	$2.95	-	Vendor C
A	$189.35	24	$7.89	-	VendorD
B	$233.22	10	$23.32	$23.32	Vendor A
B	$124.89	10	$12.49	-	Vendor C
B	$186.60	10	$9.33	-	VendorD
B	$305.98	10	$15.30	-	Vendor G
C	$39.82	1	$39.82	$39.82	VendorA
C	$62.71	1	$62.71	-	Vendor A
C	$54.89	1	$54.89	-	VendorB
C	$85.43	1	$85.43	-	Vendor C

[Oleg_Troyansky]

Hi there!

These are the kinds of solutions that I teach at my advanced Set Analysis session at the Masters Summit for Qlik - we will be in Dublin just a couple of weeks from now, check our agenda and see if you can join.

For now, here is the direction for your solution:

To calculate the lowest price per Item, you need to use Min as the outer aggregation around the AGGR() function, and the calculation for Price as the inner aggregation. Something like this:

Min ( AGGR (  Sum(Price)/sum(Qty) , Product ))

Now, this will return one result, displayed in one line per Product, but not necessarily in the correct line. To get the same results consistently in every line, use NODISTINCT for your AGGR:

Min ( AGGR (NODISTINCT  Sum(Price)/sum(Qty) , Product ))

TO show it only in the correct line, you could use IF() and compare this result to the Price column, and only show the number if it's the lowest.

If you have many "why" questions - then you should really find your way to my class. I'm teaching these techniques in a long half-day session with many hands-on examples.

Cheers,

[Noor51]

Hi, 

Thanks for the quick reply I tried using the formal  and is doesn't display the lowest each price in the or even a correct each price. Could this be a formatting issue or a  set expression issue?

Thanks,

Gurnoor

[Gabbar · Accepted solution]

Not Sure If there would be Other Columns,
Unit Price:-  aggr(Sum(Price)/Sum(Qty),Vendor,[Product Code])
Best Price:- Aggr(nodistinct Min(aggr(Sum(Price)/Sum(Qty),Vendor,[Product Code])),[Product Code])

[Oleg_Troyansky]

Hi Gurnoor,

It's hard to guess what might be wrong without seeing the data in the app. Ideally, create a small sample and share it here for us to see.

One comment about your screenshot - if you are using the Product Description field in your chart, then you should be using the same field in your AGGR dimensions, otherwise you may get some unexpected results. Your Product Codes and Product Descriptions may not align 100%, and that could be the reason for the issue.

Cheers,