Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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?
I want the solution In the script.
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;
I tried this solution but it's giving me Last date for every month. 30 or 31. And not from the data.
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;
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;
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...
Hi This is also not working.