This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
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:
LOAD Min([MM-YYYY]) AS MinDate,Max([MM-YYYY]) AS MaxDateResident [(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,
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:
LOAD Min(makedate(right([MM-YYYY],4),left([MM-YYYY],2))) AS MinDate,Max(makedate(right([MM-YYYY],4),left([MM-YYYY],2))) AS MaxDateResident [(Sheet1...)]
Hope that helps
View solution in original post
May be try this
LOAD Min(Date#([MM-YYYY]),'MM-YYYY') AS MinDate,Max(Date#([MM-YYYY]),'MM-YYYY') AS MaxDateResident [(Sheet1...)];
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.
Sorry small mistake with the parantheses
try it now
LOAD AS MinDate,Max(Date#([MM-YYYY],'MM-YYYY')) AS MaxDateResident [(Sheet1...)];
No sorry, that doesn't work.
It works! Thanks so much!
Hi again Carlos,
Would it be possible for you to explain to me how that works?
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.
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),'?