Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarkharpude
Creator III
Creator III

Max dates out of multiple dates for each year month

Hi,

I want to find out the max date for each year month. I have date like this:

21/9/2015,
5/9/2015,
12/9/2015,
1/10/2015,
5/10/2015,

So i want to load only max date for every month year. Expected output should be :

21/9/2015

5/10/2015

Thanks

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Data:

Load

  Date#(Date,'D/M/YYYY') as Date,

  Date(MonthStart(Date#(Date,'D/M/YYYY'))) as MonthYear

Inline

[

  Date, Sales

  21/9/2015, 100

  5/9/2015, 200

  12/9/2015, 300

  1/10/2015, 400

  5/10/2015, 500

];

Left Join (Data)

Load Date(Max(Date)) as MaxDate, MonthYear Resident Data

Group By MonthYear;

If you want to drop Date field, use

Drop Field Date;

View solution in original post

11 Replies
MK_QSL
MVP
MVP

Do you want solution at front end or inside script?

How you want to display these dates.. I mean in List Box or inside any chart/table?

sagarkharpude
Creator III
Creator III
Author

I want the solution In the script.

MK_QSL
MVP
MVP

Data:

Load

  Date#(Date,'D/M/YYYY') as Date,

  Date(MonthStart(Date#(Date,'D/M/YYYY'))) as MonthYear

Inline

[

  Date, Sales

  21/9/2015, 100

  5/9/2015, 200

  12/9/2015, 300

  1/10/2015, 400

  5/10/2015, 500

];

Left Join (Data)

Load Date(Max(Date)) as MaxDate, MonthYear Resident Data

Group By MonthYear;

If you want to drop Date field, use

Drop Field Date;

sagarkharpude
Creator III
Creator III
Author

I tried this solution but it's giving me Last date for every month. 30 or 31. And not from the data.

sagarkharpude
Creator III
Creator III
Author

I have this script. But it's giving me last date of every month like 30 or 31 and not from the date fields.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

LOAD

MonthStart(Date(date(Date#((Evaluate('99999999')-Num#(GDATU)),'YYYYMMDD')),'D/M/YYYY')) as MonthYear,

     Date(date(Date#((Evaluate('99999999')-Num#(GDATU)),'YYYYMMDD')),'D/M/YYYY') AS GDATE

           

FROM

[\\PA-QV-PUB\Pre-Stage Target QVDs\TCURR.qvd]

(qvd) ;

Left Join(Table)

LOAD MonthYear,

  Date(Max(GDATE)) as Date

Resident Table

Group By MonthYear;

pathiqvd
Creator III
Creator III

Hi,

  Try like this

A:

Load *,Date#(Date1,'DD/MM/YYYY') as Date2,

month(date(Date#(Date1,'DD/MM/YYYY'))) as Date_Month

inline

[

Date1

21/9/2015,

5/9/2015,

12/9/2015,

1/10/2015,

5/10/2015

];

B:

load date(max(Date2)) as max_date Resident A Group by Date_Month;

MK_QSL
MVP
MVP

The Data your provided is different compare to the data you are using, so I can't help on this.

Either provide real data or create dummy data based on real data and provide us here...

sagarkharpude
Creator III
Creator III
Author

Hi This is also not working.

Not applicable

Bonjour sagar kharpude :

Quand je poste une question mon message subi des transformations pourquoi ?