Comparing Total Collected between 2 month within the same period

[mohdhaniff]

Hi.

How to create QV expression to get a total for transaction done in current) month with previous month (or previous 2 month) within the same period (number of days in current month).

Currently I do it manually in excel sheet as per attached. I believe qlikview can do it with more systematic and faster.

Regards.

[mohdhaniff · Accepted solution]

I already got the answer for my own question and shared for my future reference.

Hope this solution can help others.

DATA LOAD::

DATA:
NoConcatenate
LOAD Distinct Serial,
[ETP Amount Finance],
Date( Date#([Date Disbursed],'DD-MMM-YYYY'),'DD-MMM-YYYY') as [Date Disbursed],
Date(Date#([Date Disbursed - Month],'MMM-YYYY'),'DD-MMM-YYYY') as [Date Disbursed - Month]
FROM (biff, embedded labels, table is Data$);

///---------------------------

Date_Temp:
NoConcatenate
LOAD Distinct [Date Disbursed],
[Date Disbursed - Month] Resident DATA;

Left Join (Date_Temp)

LOAD Distinct [Date Disbursed],[Date Disbursed - Month],AutoNumber(RecNo(),[Date Disbursed - Month]) as RecordNo
Resident Date_Temp Order By [Date Disbursed], [Date Disbursed - Month];

Left Join (Date_Temp)
LOAD Distinct Date(max([Date Disbursed - Month]),'DD-MMM-YYYY') as Mx_Date_Disbursed_Month Resident Date_Temp;



Date_Current:
NoConcatenate
LOAD RecordNo,RecordNo as [RecordNo Current] Resident Date_Temp where Mx_Date_Disbursed_Month=[Date Disbursed - Month];




Date:
NoConcatenate
LOAD Distinct * Resident Date_Temp;

Left Join (Date)

LOAD Distinct * Resident Date_Current;

DROP Tables Date_Current,Date_Temp;

///---------------------------------

Compare:
NoConcatenate
LOAD Distinct * Resident DATA;
Left Join (Compare)
LOAD Distinct * Resident Date;

DROP Tables DATA,Date;

Expressiom:

Current Month:

sum({$<[Date Disbursed - Month]={'$(=max([Date Disbursed - Month]))'}>} [ETP Amount Finance])

Previous Month:

sum({$<[Date Disbursed - Month]={'$(=Date(AddMonths(max([Date Disbursed - Month]),-1),'DD-MMM-YYYY'))'}>} if([RecordNo Current]=RecordNo,[ETP Amount Finance]))

Result: