Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Purushothaman
Partner - Creator III
Partner - Creator III

Repeat last value Month Value

Hi Experts,

Below is my data set:

Purushothaman_0-1675742263043.png

 

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

 

Purushothaman_1-1675742346176.png

 

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:

Purushothaman_2-1675742650090.png

 

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

 

Anyone, Please help!!!

Thank you!!

 

 

 

1 Solution

Accepted Solutions
ogster1974
Partner - Master II
Partner - Master II

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;

 

ogster1974_0-1675766756898.png

 

View solution in original post

3 Replies
ogster1974
Partner - Master II
Partner - Master II

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;

 

ogster1974_0-1675766756898.png

 

Purushothaman
Partner - Creator III
Partner - Creator III
Author

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
Partner - Master II
Partner - Master II

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';