How to calculate the outstanding count month by month

[#1]

I have the following set of samlpe data and should like to calculate the outstanding un-settled invoice month by month.

Data
Invoice Number	Issue Date	Settle Date
A01	1-Jan-12	30-Apr-12
A02	1-Jan-12	31-May-12
A03	1-Jan-12	31-Mar-12
A04	1-Jan-12	31-Aug-12
A05	1-Jan-12	31-Jan-12
A06	1-Mar-12	31-Mar-12
A07	1-Apr-12	31-Aug-12
A08	1-May-12	31-Aug-12
A09	1-May-12	1-Sep-12
A10	1-Jun-12	31-Dec-12

The result that I am looking for is as below:

Output
Summary	Oustanding Invoice Count
2012Jan	5
2012Feb	4
2012Mar	5
2012Apr	4
2012May	5
2012Jun	5
2012Jul	5
2012Aug	5
2012Sep	2
2012Oct	1
2012Nov	1
2012Dec	1

Anyone have any idea how to achieve this if I would like to calculate this on the chart object level instead of pre-calculate at the script level ?

Thanks in advance.

Cheers

H

[#2]

Hi

my reckon is you could use the  Intervalmatch function.

I'm not familiar with it but i think it could fit your needs

best regards

Chris

[#3]

Thanks for your reply.

But each record will need to be used several times based on the output dimension value.

For example, in my case, the invoice A01 needs to be included in the count when the dimension of output is 2012Jan, 2012Feb, 2012Mar and 2012Apr.

[kaushiknsolanki]

Hi,

     Have a look at the attached application.

Regards,

Kaushik Solanki

[#5]

Hi Kaushik,

    Thanks for your sample file. I understand this solution but I don't want to expand my data table as I am having like 3M of transaction records. If using this way, my maindata table will be like havingt more than 6M records.

Can this type of calculation being done by using expression in the chart objects?

Thanks a lot

Cheers

H

[Gysbert_Wassenaar]

If you're going to try this with chart expressions it's likely going to be painfully slow. And 3 or 6 million records should be any problem for Qlikview.

I'd try it this way:

Data:
Load [Invoice Number],Date(Date#([Issue Date],'DD-MMM-YY')) as [Issue Date],Date(Date#([Settle Date],'DD-MMM-YY')) as [Settle Date] inline [
Invoice Number, Issue Date, Settle Date
A01, 1-Jan-12, 30-Apr-12
A02, 1-Jan-12, 31-May-12
A03, 1-Jan-12, 31-Mar-12
A04, 1-Jan-12, 31-Aug-12
A05, 1-Jan-12, 31-Jan-12
A06, 1-Mar-12, 31-Mar-12
A07, 1-Apr-12, 31-Aug-12
A08, 1-May-12, 31-Aug-12
A09, 1-May-12, 1-Sep-12
A10, 1-Jun-12, 31-Dec-12
];

Result:
load [Invoice Number], date(addmonths([Issue Date],IterNo()-1)) as Month
Resident Data
while addmonths([Issue Date],IterNo()-1) < [Settle Date]; // or maybe <= instead of <

If necessary you can calculate the counts too in the script.

Summary:
load Month, count([Invoice Number]) as InvoiceCount
resident Result
group by Month;

drop table Result;

[#7]

Thanks Gysbert. I understand what you mean.

Let me see what can I do.

The reason that we would like to calculate this on the chart is because we don't want to make any changes on top of the existing data model.

Thanks all again!