First Price Calculation

[andrea90casa]

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

[vishsaggi]

Can you share a sample file to look into?

[andrea90casa]

Hi Vishwarath

Here you are

[vishsaggi]

Try this in your price min data

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

[andrea90casa]

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)

[avkeep01]

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)

[antoniotiman]

Try this

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

[andrea90casa]

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

[avkeep01 · Accepted solution]

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;

[andrea90casa]

Perfect, Thank you very much