Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max date function in set analysis

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

1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

8 Replies
oknotsen
Master III
Master III

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 you live in interesting times!
sunny_talwar

May be try this:

=Sum({<Prijshistorie_Peildatum={"$(=Date(Max(Prijshistorie_Peildatum), 'YourDateFieldFormat'))"}>} pvprijs)

Not applicable
Author

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,

Kushal_Chawda

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

oknotsen
Master III
Master III

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.

May you live in interesting times!
sunny_talwar

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

Kushal_Chawda

sunny,

I changed the expression:)

sunny_talwar

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.