topic Re: Max date function in set analysis in QlikView

Max date function in set analysis

Wed, 28 Oct 2015 13:49:12 GMT

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

Re: Max date function in set analysis

oknotsen — Wed, 28 Oct 2015 13:54:06 GMT

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)

Re: Max date function in set analysis

sunny_talwar — Wed, 28 Oct 2015 14:01:32 GMT

May be try this:

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

Re: Max date function in set analysis

Wed, 28 Oct 2015 14:09:52 GMT

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,

Re: Max date function in set analysis

Kushal_Chawda — Wed, 28 Oct 2015 14:16:53 GMT

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

Re: Max date function in set analysis

oknotsen — Wed, 28 Oct 2015 14:19:45 GMT

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.

Re: Max date function in set analysis

sunny_talwar — Wed, 28 Oct 2015 14:19:54 GMT

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

Re: Max date function in set analysis

Kushal_Chawda — Wed, 28 Oct 2015 14:21:01 GMT

sunny,

I changed the expression:)

Re: Max date function in set analysis

sunny_talwar — Wed, 28 Oct 2015 14:22:49 GMT

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.