topic Re: Get FirstValue of next Month script in QlikView

Get FirstValue of next Month script

pgalvezt — Wed, 08 Jan 2020 16:12:19 GMT

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!!

Re: Get FirstValue of next Month script

Arthur_Fong — Thu, 09 Jan 2020 03:31:48 GMT

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;

Re: Get FirstValue of next Month script

pgalvezt — Wed, 29 Jan 2020 21:39:34 GMT

Hi, a couple questions...

Could You please explain the code ?

How can I read several years?

Thanks!!

Re: Get FirstValue of next Month script

Arthur_Fong — Thu, 30 Jan 2020 06:16:20 GMT

No issue reading multiple years from my side:

Re: Get FirstValue of next Month script

pgalvezt — Thu, 30 Jan 2020 13:32:00 GMT

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!

Re: Get FirstValue of next Month script

Arthur_Fong — Fri, 31 Jan 2020 00:55:40 GMT

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;

Re: Get FirstValue of next Month script

Brett_Bleess — Mon, 10 Feb 2020 20:14:53 GMT

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