Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Can you share a sample file to look into?
Hi Vishwarath
Here you are
Try this in your price min data
= Aggr(avg({< data = {"= Aggr( min(data), Product)" } > }Unit_Price), Product)
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)
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)
Try this
Aggr(NODISTINCT FirstSortedValue(DISTINCT Unit_Price,data),Product)
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
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;
Perfect, Thank you very much