topic help with a formula max in App Development

help with a formula max

ezec1807 — Wed, 21 Nov 2018 12:46:14 GMT

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

Re: help with a formula max

Thiago_Justen_ — Wed, 21 Nov 2018 12:57:57 GMT

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))

Re: help with a formula max

ezec1807 — Wed, 21 Nov 2018 20:34:30 GMT

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

Re: help with a formula max

Thiago_Justen_ — Wed, 21 Nov 2018 20:55:22 GMT

Could be this?

FirstSortedValue ({<high_date = {"> = $ (vFecFrom) <= $ (vFecTo)"}>}Price, Aggr (Date, Product))

Re: help with a formula max

ezec1807 — Thu, 22 Nov 2018 00:41:25 GMT

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

Re: help with a formula max

Anil_Babu_Samineni — Thu, 22 Nov 2018 00:53:52 GMT

Try this?

FirstSortedValue({<high_date = {">=$(vFecFrom)<=$(vFecTo)"}>} Price, Aggr (Date, Product))

Re: help with a formula max

ezec1807 — Thu, 22 Nov 2018 01:01:17 GMT

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