Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ezec1807
Creator
Creator

help with a formula max

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

6 Replies
Thiago_Justen_

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

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
ezec1807
Creator
Creator
Author

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

Thiago_Justen_

Could be this?

 

FirstSortedValue ({<high_date = {"> = $ (vFecFrom) <= $ (vFecTo)"}>}Price, Aggr (Date, Product))
Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
ezec1807
Creator
Creator
Author

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
Anil_Babu_Samineni

Try this?

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

 

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ezec1807
Creator
Creator
Author

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