Repeat last value Month Value

[Purushothaman]

Hi Experts,

Below is my data set:

Below is the output of  a  grouped Bar chart based on the Above dataset.

My Requirement is :

If a "client grouping" is not present on Monthyear, It should take previous month "Client grouping" number of "Cummulative count of Client Code". 

For Example:

1) FD was not present in Mar-2010, so it should take from Feb-2010.

2) FD was not present in Jan-2011, so it should take last presented data value of FD from  Feb-2010

3) Bonds was not present in Jan-2011,  so it should take from Mar-2010.

Below is my illustration of my requirement:

I have already created "Cummulative count of Client Code". I have attached the QVF. 

Anyone, Please help!!!

Thank you!!

[ogster1974 · Accepted solution]

Add some dummy data into your dataset with 0 Client Count.

NoConcatenate
ClientsTmp:
Load
Distinct
ClientGrouping,
ClientCode
Resident data
;
Join(ClientsTmp)
Load
Distinct
MonthYear
Resident data
;

NoConcatenate
Clients:
Load
MakeDate(SubField(MonthYear,'-',2), Num(Month(Date#(SubField(MonthYear,'-',1),'MMM'))), 1) as "dd/mm/yyyy",
MonthYear, ClientGrouping, ClientCode,
0 as ClientCodeCount
Resident ClientsTmp
;

Drop table ClientsTmp;

NoConcatenate
data1:
Load
*
Resident data;
Concatenate(data1)
Load
*
Resident Clients;

Drop tables data, Clients;

data2:
Load
If(RowNo()=1,
ClientCodeCount,
If(ClientGrouping=Peek(ClientGrouping),
Peek([CummulativeCountOfClientCode per Date and ClientGrouping])+ClientCodeCount,
ClientCodeCount
)
) AS [CummulativeCountOfClientCode per Date and ClientGrouping],
*
Resident data1
Order By ClientGrouping, "dd/mm/yyyy", MonthYear,ClientCode;

Drop Table data1;

[Purushothaman]

Hi @ogster1974 ,

Many thanks for your help!! Possible to share your working QVF file, As I am getting difference in my output.

Thank you!!

[ogster1974]

In the main section I set the date environment variables to match the content coming in.

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';