Monthly data to yearly basis using first month data each year

[#1]

Hi,

I have a set of monthly data of number of existing customers as below:

200901      234

200902      233

200903      245

200904      243

...

201707      252

201708      255

I would like to use just the first month's data of each year and the most recent month's data to compare on a yearly basis

like this:

200901      234

201001      244

201101      246

201201      254

...

201701      246

201708      255

How could I do this?

Thank you:)

[Anil_Babu_Samineni]

Are you expecting MonthStart() function ??

[eduardo_dimperio]

Hey Stacey,

Maybe you could do that

MaxDate:

LOAD

Max(YourDate) AS Max_Date

FROM

YourQVD

Let vMaxDate = Max_Date;

Date_ByMonth:

LOAD

YourDate,

Max(YourDate) AS Max_Date_ByMonth

FROM

YourQVD

Group by

YourDate

Difference:

LOAD

YourDate,

Interval($(Max_Date)-Max_Date_ByMonth,'D') AS Days_of_Difference

FROM

resident YourDate

[AxNoxy]

Something like this?,  I'll add the sample too,

Regards.

[#5]

Hi,

Thank you for the reply. But after sorting the data, how should I display just the January data of each year followed by the most recent month? I need to discard the rest.

Thanks