Get Previous Month value to next month and so on

[shahzad]

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

[morgankejerhag]

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;