topic Re: Comparing different price values from a single product in QlikView

Comparing different price values from a single product

thanhphongle — Thu, 03 Aug 2017 09:05:40 GMT

Hello Community,

this is my table

Date	Product	Price
01.01.2016	A	100 €
01.01.2016	A	95 €
01.02.2016	A	95 €
01.03.2016	A	80 €
01.04.2016	A	80 €
01.01.2017	A	49 €
01.01.2016	B	1000 €
01.01.2016	B	950 €
01.02.2016	B	950 €
01.03.2016	B	800 €
01.04.2016	B	800 €
01.01.2017	B	300 €

is there a way to compare different price values from particular products? For example

if min(Price of a Product) is <= 50 % of max(Price of Product) , then sum(All min(Price of a Product) is <= 50 % of max(Price of Product))

Step 1

min[Product(A)] = 49€ is 50% smaller or more than 50% smaller than max[Product(A)]=100 €

min[Product(B)] = 300€ is 50% smaller or more than 50% smaller than max[Product(B)]=1000 €

Step 2

sum up all products 49€ + 300€ = 349€

I hope anyone can help me out here.

Re: Comparing different price values from a single product

prat1507 — Thu, 03 Aug 2017 09:26:27 GMT

Where do you want to show the Final sum, do I need to add an extra column, or replace the min value for each product?

Regards
Pratyush

Re: Comparing different price values from a single product

simotrab — Thu, 03 Aug 2017 09:30:15 GMT

Let's proceed with order, first we need the mins and the maxs:

min({$<Product={"A"}>}price)

min({$<Product={"B"}>}price)

max({$<Product={"A"}>}price)

max({$<Product={"B"}>}price)

So if I've understood well, maybe this could work (I've not tested yet).

if( min({$<Product={"A"}>}price)<=max({$<Product={"A"}>}price)*0.5,min({$<Product={"A"}>}price))

if( min({$<Product={"B"}>}price)<=max({$<Product={"B"}>}price)*0.5,min({$<Product={"B"}>}price))

I think that with a proper set analysis it could be better.

Re: Comparing different price values from a single product

Kushal_Chawda — Thu, 03 Aug 2017 09:32:42 GMT

Data:

LOAD Date,

Product,

Price

FROM

[https://community.qlik.com/thread/269939]

(html, codepage is 1252, embedded labels, table is @1);

Left Join

LOAD Product,

min(Price) as MinPrice,

max(Price) as MaxPrice

Resident Data

Group by Product;

Final:

LOAD *,

if(MinPrice/MaxPrice<=0.5,1) as Flag

Resident Data;

DROP Table Data;

Left Join(Final)

LOAD sum(DISTINCT MinPrice) as SumofAllProductPrice

Resident Final

where Flag=1;

Re: Comparing different price values from a single product

thanhphongle — Thu, 03 Aug 2017 09:37:56 GMT

Hello Simone,

thank you for your help. The solution should consider all kind of products. In my example I listed only product A and B. But for the case I have more products the solution should sum up all product to the mentioned conditions. So I think it would be really time-consuming

Re: Comparing different price values from a single product

thanhphongle — Thu, 03 Aug 2017 09:39:39 GMT

Hello Pratyush,

I' d like to show the sum in a textbox. The solution should consider all kind of products not only A and B. The product A and B was just an example.

Re: Comparing different price values from a single product

thanhphongle — Thu, 03 Aug 2017 09:48:32 GMT

Hello Kushal,

I'm about to try your solution. Is there another way to solve my problem instead of manipulating the script?

Re: Comparing different price values from a single product

Kushal_Chawda — Thu, 03 Aug 2017 09:52:15 GMT

This expression should work

=sum(aggr(if(min(Price)/max(Price)<=0.5,min(Price)),Product))

Re: Comparing different price values from a single product

thanhphongle — Thu, 03 Aug 2017 10:06:32 GMT

This is expression works great!!

Thank you very much!!

Re: Comparing different price values from a single product

Kushal_Chawda — Thu, 03 Aug 2017 10:34:37 GMT

Glad that it worked