topic Re: Monthly data to yearly basis using first month data each year in QlikView

Monthly data to yearly basis using first month data each year

Wed, 25 Nov 2020 16:16:04 GMT

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:)

Re: Monthly data to yearly basis using first month data each year

Anil_Babu_Samineni — Thu, 31 Aug 2017 16:27:42 GMT

Are you expecting MonthStart() function ??

Re: Monthly data to yearly basis using first month data each year

eduardo_dimperio — Thu, 31 Aug 2017 17:22:43 GMT

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

Re: Monthly data to yearly basis using first month data each year

AxNoxy — Thu, 31 Aug 2017 18:25:38 GMT

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

Regards.

Re: Monthly data to yearly basis using first month data each year

Fri, 01 Sep 2017 08:42:05 GMT

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