Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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;
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;
Hi @ogster1974 ,
Many thanks for your help!! Possible to share your working QVF file, As I am getting difference in my output.
Thank you!!
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';