Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I need help with a formula.
I have to obtain the price of a product of its maximum date and its minimum date and that is shown by product. I have a graph with the product dimension, and for each one I have to show the maximum and minimum price.
Example:
Product, Date, Price
A, 01/01/2018, 100
A, 03/11/2018, 150
A, 2/23/2018, 200
B, 03/11/2017, 125
B, 07/02/2016, 300
C, 05/15/2018, 100
Correct result (max date)
Product, Date, Price
A, 03/11/2018, 150
B, 03/11/2017, 125
C, 05/15/2018, 100
Correct result (min date)
Product, Date, Price
A, 01/01/2018, 100
B, 07/02/2016, 300
C, 05/15/2018, 100
I tried this formula but the values are not as expected:
= Max ({<Date = {$ (= Max (Date))}>} Price)
Thank you very much for the help!
regards
For Min value you can do this (in the UI):
FirstSortedValue(Price,Aggr(Date,Product))
For Max value you can do this (in the UI):
FirstSortedValue(Price,-Aggr(Date,Product))
Thank you!
I would have to apply a set analysis within the aggr, so that it filters me only the dates that coincide with another date field.
I would have to put a compliment. How would he do it?
It would be something like that what I need:
FirstSortedValue (Price, Aggr ({<high_date = {"> = $ (vFecFrom) <= $ (vFecTo)"}>} Date, Product))
Thank you!
regards
Could be this?
FirstSortedValue ({<high_date = {"> = $ (vFecFrom) <= $ (vFecTo)"}>}Price, Aggr (Date, Product))
Thanks again!
I had to modify the expression a bit.
I put:
FirstSortedValue (Price, Aggr ({<high_date = {"> = $ (vFecFrom) <= $ (vFecTo)"}>} Date, Product))
If I put this formula in a table chart, it's perfect!
If I put it in a text box it does not give me any value. In other words, in this table, I should show a single value because I would be evaluating all the records that I have available depending on the filter, that is, I should put an aggregation, but still it does not show output value.
Thank you!!!
regards
Try this?
FirstSortedValue({<high_date = {">=$(vFecFrom)<=$(vFecTo)"}>} Price, Aggr (Date, Product))
Yes, I tested it previously and I had to modify it for the one I just reported so that I can take it well. But the problem now is when I want to put it in a text box.
Thank you!
regards