topic Re: First Price Calculation in QlikView

First Price Calculation

andrea90casa — Wed, 15 Nov 2017 16:46:24 GMT

Hi at all,

I have this situation

So i have several products and the date of purchase and the relative unit price.

I created a field called "Prima data" where i select, for each product, the first date of purchased and then i would like to obtain, for each product, the price of purchased at the first date. The problem is that in the last column i used this formula

aggr(nodistinct avg({<data={"$(MinData)"}>}Unit_Price), Product)

but it calc the unit price only for the first date of the entire data set (in this example was product A purchased at 01/01/2017 at 1,1).

Is there any solution in order to calc the first price for each product?

Andrea

Re: First Price Calculation

vishsaggi — Wed, 15 Nov 2017 16:53:04 GMT

Can you share a sample file to look into?

Re: First Price Calculation

andrea90casa — Wed, 15 Nov 2017 16:55:08 GMT

Hi Vishwarath

Here you are

Re: First Price Calculation

vishsaggi — Wed, 15 Nov 2017 17:14:02 GMT

Try this in your price min data

= Aggr(avg({< data = {"= Aggr( min(data), Product)" } > }Unit_Price), Product)

Re: First Price Calculation

andrea90casa — Thu, 16 Nov 2017 14:01:31 GMT

Thanks Vish

I tried with min function and it works, then i tried to change min with max function but the result doesn't change.

For example for Product E i would like to see 1,2 as min data and 1,22 as max data

= Aggr(avg({< data = {"= Aggr( min(data), Product)" } > }Unit_Price), Product)

= Aggr(avg({< data = {"= Aggr( max(data), Product)" } > }Unit_Price), Product)

Re: First Price Calculation

avkeep01 — Thu, 16 Nov 2017 14:22:46 GMT

Hi Andrea, Are you using the expression in the chart with the dimension Product? Then you can drop the aggregation on product within the Set analysis.

= Aggr(ONLY({< data = {"$(= MAX(data))" } > }Unit_Price), Product)

Re: First Price Calculation

antoniotiman — Thu, 16 Nov 2017 14:47:49 GMT

Try this

Aggr(NODISTINCT FirstSortedValue(DISTINCT Unit_Price,data),Product)

Re: First Price Calculation

andrea90casa — Thu, 16 Nov 2017 14:59:23 GMT

Thanks A.M.

Now it works but when i select only one product (and it could be useful). But how can i do if i would like to obtain an aggregated result, for example:

I have list of product with first price and last price, and i have the total quantity of product purchased.

I can create two purchased amount:

First Price * Quantity

Last Price * Quantity

And then i can subract the second term to the first in order to obtain a simulated saving for each product.

then it could be useful sum all these amount (for a specific category of purchase for example) and obtain a total saving for each category.

But with only function i can't do that i supposed

Re: First Price Calculation

avkeep01 — Thu, 16 Nov 2017 15:17:15 GMT

Hi Andrea,

Are you able to update the script? In that case you can add the script below:

dataset:

LOAD data,

Prodotto as Product,

Prezzo_Uni as Unit_Price,

Categoria as Category,

Quantità as Quantity,

Conto

FROM

(ooxml, embedded labels, table is Foglio1);

LEFT JOIN LOAD

Product,

MIN(data) AS data,

1 AS IsFirstPrice

RESIDENT dataset

GROUP BY Product;

LEFT JOIN LOAD

Product,

MAX(data) AS data,

1 AS IsLastPrice

RESIDENT dataset

GROUP BY Product;

Re: First Price Calculation

andrea90casa — Thu, 16 Nov 2017 15:27:06 GMT

Perfect, Thank you very much

Re: First Price Calculation

antoniotiman — Thu, 16 Nov 2017 15:35:20 GMT

You can

Aggr(NODISTINCT FirstSortedValue(DISTINCT Unit_Price,data),Product) *Quantity

Last Price -> replace data with -data