Issue with MinMax

[#1]

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

[aarkay29]

May be try this

MinMax:

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

[#3]

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.

[aarkay29]

Sorry small mistake with the parantheses

try it now

MinMax:

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

[#5]

Hi again,

No sorry, that doesn't work.

[CarlosAMonroy · Accepted solution]

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

[#7]

Hi Carlos,

It works! Thanks so much!

[#8]

Hi again Carlos,

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

Best,
Alison

[CarlosAMonroy]

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

[#10]

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),'?