Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Im trying to use a max date function in set analysis and this is the result after some digging in this forum.
sum({<Prijshistorie_Peildatum={'$(=max(Prijshistorie_Peildatum))'}>}pvprijs)
However, it does not work....
It should only display the price of the most recent date. (as determined by ''Prijshistorie_Peildatum'')
can someone spot what i did wrong?
kind regards,
Aart Noordegraaf
If Prijshistorie_Peildatum is text (right aligned in listbox ) then use below expression
sum({<Prijshistorie_Peildatum={"$(=date#(max(Prijshistorie_Peildatum),'DD/MM/YYYY'))"}>}pvprijs)
sum({<Prijshistorie_Peildatum={"$(=date(max(date#(Prijshistorie_Peildatum,'DD/MM/YYYY')),'DD/MM/YYYY'))"}>}pvprijs)
If Prijshistorie_Peildatum is in Date format ( left aligned in listbox ) then use below expression
sum({<Prijshistorie_Peildatum={"$(=date(max(Prijshistorie_Peildatum),'DD/MM/YYYY'))"}>}pvprijs)
I have assumed that your date for mat is DD/MM/YYYY. You can change the format according to your data
Can you tell us what does not work?
My guess is it shows the maximum Peildatum of your current selection. If you want it to show the maximum date while ignoring your selection, try this (notice the tiny thing I added to your max function):
sum({<Prijshistorie_Peildatum={'$(=max({1} Prijshistorie_Peildatum))'}>}pvprijs)
May be try this:
=Sum({<Prijshistorie_Peildatum={"$(=Date(Max(Prijshistorie_Peildatum), 'YourDateFieldFormat'))"}>} pvprijs)
Thanks for the quick response.
The value returns zero instead of the price I want it to return, even if i add the {1} as u suggest.
Let me explain it very simply:
I have one product with a price history. When a price changes the date is recorded in : Prijshistorie_Peildatum
So irrgardless of what i select for time period I want to see the most recent price for the product.
Kind regards,
If Prijshistorie_Peildatum is text (right aligned in listbox ) then use below expression
sum({<Prijshistorie_Peildatum={"$(=date#(max(Prijshistorie_Peildatum),'DD/MM/YYYY'))"}>}pvprijs)
sum({<Prijshistorie_Peildatum={"$(=date(max(date#(Prijshistorie_Peildatum,'DD/MM/YYYY')),'DD/MM/YYYY'))"}>}pvprijs)
If Prijshistorie_Peildatum is in Date format ( left aligned in listbox ) then use below expression
sum({<Prijshistorie_Peildatum={"$(=date(max(Prijshistorie_Peildatum),'DD/MM/YYYY'))"}>}pvprijs)
I have assumed that your date for mat is DD/MM/YYYY. You can change the format according to your data
I now notice the single quotes. Remove those and try again.
Alternatively:
Make a Straight table with in there only the products and the max({1} Prijshistorie_Peildatum) as expression to see what it results in.
Giving it more thought it might be that only the Products that have a Prijs with a Peildatum being the same as the maximum Peildatum will show up. So if the maximum Peildatum of a products is not the lower, it will not give a result.
Hi Kush
Correct me if you think I am wrong, but if Prijshistorie_Peildatum is text how will you compare it to a date field? and even before that how would you calculate the max(ofTextField)?
I think that transformation would have to take in the back end of the script.
Update: I guess you fixed part of my questions
sunny,
I changed the expression:)
I still feel that if Prijshistorie_Peildatum is text, the expression won't work because you would be comparing text to a date field. May be if you add Text() it might work. But have never tried that before so not sure if that will work or not.