Discussion Board for collaboration on QlikView Scripting.
I have a collection of records with dates. I want to select the highest day of each month. for example:
Article1 - 01-08-2013
Article1 - 10-08-2013
Article2 - 05-08-2013
Article2 - 14-08-2013
I want to select:
I want to do this in script. Something like MaxDay(), but MaxDate from the specific month.
Go to Solution.
FirstSortedValue(SalesValue, -Date) as SalesValueAtMaxDate,
Maybe like this
LOAD Article, Date FROM ...;
Date(max(Date) as MaxDate
Group By Article, monthstart(Date);
This does not work for me. I get a script error.
Seems that I missed a closing bracket for the Date() function.
After correcting this, works ok for me:
SET DateFormat = 'DD-MM-YYYY';
LOAD * INLINE [
Date(max(Date)) as MaxDate
Copy and paste this script
Group by Article;
SEE ATTACHED FILE
Thank you. Now this works. However, I have more columns connected to these articles. Lets say:
Article1 - 01-08-2013 - €10
Article1 - 10-08-2013 - €12
Article2 - 05-08-2013 - € 13
Article2 - 14-08-2013 - € 9
Article1 - 10-08-2013 - € 12
How can I put this in the provided code? The group by code seem to be troubling
LASTVALUE(Date) AS Date
GROUP BY Article, MONTHSTART(Date);