Get FirstValue of next Month script

[pgalvezt]

Hello!

Actually I have the next Code that get the last value of actual month.

LOAD

Date(Max(Date_Value)) as Fecha_Go,
Period_Go as Period,
FirstSortedValue(U_GO, -Date_Value) as GO

Resident Tabla_B
Where len(U_GO)> 0
Group By Period_Go ;

And now I would like to get the first value the next month,

For example if January 2019 is selected I need to show the first value of February an so on..

If just 2019 its selected the table should show:

Jan                                           Feb

First value of February    First Value of March and so on

I Attached an Excel File with an Example.

Thanks!!

[Arthur_Fong]

Try this:

Script:

LOAD Date,
	 text(month(Date))as Month, 
     Value
FROM
[C:\Users\jfong2\Downloads\Example.xlsx]
(ooxml, embedded labels, table is Data);

MonthLoop:
load
distinct
Month
resident Data;

let vRow =NoOfRows('MonthLoop');
for i=1 to '$(vRow)'


let vMonth =peek('Month',$(i)-1,'MonthLoop');
Data2:
load Date(min(Date),'DD/MM/YYYY')as Date2,
	1 as Flag
	resident Data
	where Value>0
	and Month='$(vMonth)'
	group by
	Month;
next i

left join(Data2)
load Date as Date2,
	date(Date,'YYYYMM') as YearPeriod, 
     Value  resident Data;
     
drop table Data;
DROP TABLE MonthLoop;

NoConcatenate
Data3:
load TEXT(DATE(addmonths(YearPeriod,-1),'YYYYMM')) as YearPeriod,
	 Value as NextMonthValue,
	 Flag
resident Data2;
 
join(Data3)
load Date2,
	 Year(Date2)as Year,text(YearPeriod) as YearPeriod,
	 Value,
	 Flag resident Data2;
drop table Data2;

[pgalvezt]

Hi, a couple questions...

Could You please explain the code ?

How can I read several years?

Thanks!!

[Arthur_Fong]

No issue reading multiple years from my side:

[pgalvezt]

Hello,

I entered more data for 2018 and 2020, interestingly the code is taking the data backwards and the data of 2019 is being lost, 2020 is not showing. maybe, I'm doing something wrong?

Thanks!

[Arthur_Fong]

Try this:

LOAD Date,
	 text(Date(Date,'YYYYMM'))as Month, 
     Value
FROM
[C:\Users\jfong2\Downloads\Example.xlsx]
(ooxml, embedded labels, table is Data);

MonthLoop:
load
distinct
Month
resident Data;

let vRow =NoOfRows('MonthLoop');
for i=1 to '$(vRow)'


let vMonth =peek('Month',$(i),'MonthLoop');
Data2:
load Date(min(Date),'DD/MM/YYYY')as Date2,
	1 as Flag
	resident Data
	where Value>0
	and Month='$(vMonth)'
	group by
	Month;
next i

left join(Data2)
load Date as Date2,
	date(Date,'YYYYMM') as YearPeriod, 
     Value  resident Data;
     
drop table Data;
DROP TABLE MonthLoop;

NoConcatenate
Data3:
load TEXT(DATE(addmonths(YearPeriod,-1),'YYYYMM')) as YearPeriod,
	 Value as NextMonthValue,
	 Flag
resident Data2;
 
join(Data3)
load Date2,
	 Year(Date2)as Year,text(YearPeriod) as YearPeriod,
	 Value,
	 Flag resident Data2;
drop table Data2;

exit SCRIPT;

[Brett_Bleess · Accepted solution]

Did Arthur's latest post get you what you needed?  If so, do not forget to return to the thread and use the Accept as Solution button on that post to give him credit for the assistance, and this also let's other Members know that did work.  If you are still trying to get things to work, leave an update, only way you are going to get additional posts at this point, as most folks will assume Arthur got things covered, so please be sure to either close things out or leave an update.

Regards,
Brett