Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue with MinMax

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

1 Solution

Accepted Solutions
CarlosAMonroy
Creator III
Creator III

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

View solution in original post

12 Replies
aarkay29
Specialist
Specialist

May be try this

MinMax:

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

Not applicable
Author

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
Specialist
Specialist

Sorry small mistake with the parantheses

try it now

MinMax:

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

Not applicable
Author

Hi again,

No sorry, that doesn't work.

CarlosAMonroy
Creator III
Creator III

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

Not applicable
Author

Hi Carlos,

It works! Thanks so much!

Not applicable
Author

Hi again Carlos,

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

Best,
Alison

CarlosAMonroy
Creator III
Creator III

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

Not applicable
Author

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