Get Previous Month value to next month and so on

shahzad — Sun, 23 Mar 2025 22:32:41 GMT

Dears

First of all please check the attached sample data.

I want to send any previous values to next all months.

For ex. In the sample data, for ID 2628 , the max date is 31/Dec/2024 and Amount is 342596.

What I need is, for Id 2628, the same amount (342596) should show in next all months until Current month.

So new rows to be added like

31/Jan/2024 342596

28/Feb/2024 342596

31/Mar/2024 342596

....

...

31/Mar/2025 342596

Additionally, If any month is missing then last month value should be added to the missing month.

Conclusion: For each ID there should be every months transactions from its first Month.

Thanks

Re: Get Previous Month value to next month and so on

morgankejerhag — Mon, 24 Mar 2025 07:10:47 GMT

I would solve it like this:

let vCurrentYearMonth = year(today()) & num(month(today()),'00');

Data:

Load

Year & num(month(MonthNum),'00') as YearMonth

;

LOAD

ID,

"Date",

Amount,

"Year",

"Month",

match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as MonthNum,

isMaxDate,

isMaxDate1,

0 as FlagGenerated

FROM [lib://Morgan Qlik Community:DataFiles/SampleData.xlsx]

(ooxml, embedded labels, table is Sheet1);

MaxData:

Load

ID,

max(Date) as MaxDate

resident Data group by ID;

left join

Load

ID,

Date as MaxDate,

Amount as MaxAmount,

YearMonth as MaxYearMonth

resident Data;

GenerateData:

Load

ID,

monthend(addmonths(MaxDate,iterno())) as Date,

MaxAmount as Amount,

year(addmonths(MaxDate,iterno())) as Year,

text(month(addmonths(MaxDate,iterno()))) as Month,

month(addmonths(MaxDate,iterno())) as MonthNum,

year(addmonths(MaxDate,iterno())) & num(month(addmonths(MaxDate,iterno())),'00') as YearMonth,

1 as FlagGenerated

resident MaxData while year(addmonths(MaxDate,iterno())) & num(month(addmonths(MaxDate,iterno())),'00')<=$(vCurrentYearMonth);

drop table MaxData;

Concatenate (Data)

Load * resident GenerateData;

drop table GenerateData;

topic Get Previous Month value to next month and so on in App Development

Get Previous Month value to next month and so on

Re: Get Previous Month value to next month and so on