topic Re: Issue with MinMax in QlikView

Issue with MinMax

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am trying to calculate the earliest and the latest date using the max and min function.

So the script I'm writing looks like this:

MinMax:

LOAD
Min([MM-YYYY]) AS MinDate,
Max([MM-YYYY]) AS MaxDate
Resident [(Sheet1...)];

However, when I load it and go to the UI the fields are available but they do not generate any values.

Please can you help me with this? Let me know if you need for info in order to help me.

Thanks in advance,

Alison

Re: Issue with MinMax

aarkay29 — Wed, 28 Jun 2017 14:59:56 GMT

May be try this

MinMax:

LOAD
Min(Date#([MM-YYYY]),'MM-YYYY') AS MinDate,
Max(Date#([MM-YYYY]),'MM-YYYY') AS MaxDate
Resident [(Sheet1...)];

Re: Issue with MinMax

Wed, 28 Jun 2017 15:02:51 GMT

Hi Aar,

Unfortunately it doesn't change anything,

When I drag the field into the canvas there are still no values attached to MinDate or MaxDate. Presumably, there should be one field value for both.

Re: Issue with MinMax

aarkay29 — Wed, 28 Jun 2017 15:11:18 GMT

Sorry small mistake with the parantheses

try it now

MinMax:

LOAD
AS MinDate,
Max(Date#([MM-YYYY],'MM-YYYY')) AS MaxDate
Resident [(Sheet1...)];

Re: Issue with MinMax

Wed, 28 Jun 2017 15:14:49 GMT

Hi again,

No sorry, that doesn't work.

Re: Issue with MinMax

CarlosAMonroy — Wed, 28 Jun 2017 15:16:21 GMT

Hi Alison,

I guess it is because the MM-YYYY is being interpreted as text instead of number in Qlikview. So when you apply the Max and Min function it does not return any values.

You can try the following:

MinMax:

LOAD
Min(makedate(right([MM-YYYY],4),left([MM-YYYY],2))) AS MinDate,
Max(makedate(right([MM-YYYY],4),left([MM-YYYY],2))) AS MaxDate
Resident [(Sheet1...)]

Hope that helps

Carlos M

Re: Issue with MinMax

Wed, 28 Jun 2017 15:20:28 GMT

Hi Carlos,

It works! Thanks so much!

Re: Issue with MinMax

Wed, 28 Jun 2017 15:23:29 GMT

Hi again Carlos,

Would it be possible for you to explain to me how that works?

Best,
Alison

Re: Issue with MinMax

CarlosAMonroy — Wed, 28 Jun 2017 15:35:38 GMT

Sure, as the MM-YYYY field is coming as text, I want QV to get those values as numbers in order to be able to calculate and know the min and max values.

So Makedate() function returns a date calculated from the year YYYY, the month MM and the day DD. If there is no Month given, 1(January) is assumed, same for the day.

Now you already have MM and YYYY so I'm using right() and left() functions to trim the field and take only the values I need to pass as parameters. Makedate(YYYY,MM,DD).

Does that make sense? Hope I could express myself correctly.

Carlos

Re: Issue with MinMax

Wed, 28 Jun 2017 15:41:21 GMT

That makes sense thank you. But please can you tell me, what are the numbers. For example the '4' sound here 'Min(makedate(right([MM-YYYY],4),'?

Re: Issue with MinMax

CarlosAMonroy — Wed, 28 Jun 2017 15:57:38 GMT

‌left and rigth functions take the number of characters you input in the parameters.

So, in order to take just the year from MM-YYYY field. I use rigth as the star point and then use the number 4 to take just 4 characters. and then do the same for the month, but now I want to start from the left side.

Re: Issue with MinMax

Wed, 28 Jun 2017 15:58:37 GMT

Great, thanks a lot!

Re: Issue with MinMax

oscar_ortiz — Wed, 28 Jun 2017 16:20:08 GMT

I would use caution when using the Min and Max functions, sure it works well with a small table, but a on a very large table it can take quite a long time. I know it's taught in class but it's not necessarily the best method of taking care of this.

If you look at rwunderlich‌ post https://qlikviewcookbook.com/2015/05/better-calendar-scripts/‌ you'll see he talks about this in quite a bit of detail.

Good luck

Oscar