Comparing different price values from a single product

[thanhphongle]

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.

[prat1507]

Hi

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

[simotrab]

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.

[Kushal_Chawda]

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;

[thanhphongle]

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

[thanhphongle]

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.

[thanhphongle]

Hello Kushal,

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

[Kushal_Chawda · Accepted solution]

This expression should work

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

[thanhphongle]

This is expression works great!!

Thank you very much!!

[Kushal_Chawda]

Glad that it worked