Date ranges

[#1]

Hi community!

I have the following data:

Issuing Bank	Due Date (DD/MM/YYYY)	Amount
Bank1	30/05/2013	1,100.00
Bank1	20/05/2013	800.00
Bank1	15/06/2013	2,000.00
Bank2	25/05/2013	900.00
Bank2	18/05/2014	1,300.00
Bank2	18/05/2017	1,000.00

And I need to show in QlikView a table like this:

Issuing Bank	8 days	8 days to 30 days	30 days to 90 days	90 days to 180	180 days to 1 year	Over 5 years	TOTAL
Bank1	800.00	1,100.00	2,000.00	0.00	0.00	0.00	3,900.00
Bank2	0.00	900.00	0.00	0.00	1,300.00	1,000.00	3,200.00

How do I do?

Thanks in advance!

[#2 · Accepted solution]

Hi there,

I think you have to create a chart in which you are using Issuing Bank as dimension and use expressions like

sum({<Due Date={">$(=Today())<$(=Today()+9)"}>}Amount),

sum({<Due Date={">$(=Today()+8)<$(=Today()+31)"}>}Amount), etc.

So you sum up all amounts for a specific Bank where the due date is between Today+X and Today+Y, where X is the lower bound an Y the upper bound in days. Use Sum(Amount) for your last column.

Hope this helps you.

[jpapador]

A nested if statement in the script would work.  For example:

If([Dub Date]) = Today() + 8, '8 Days',

     If([Due Date] > Today() +8 and < Today() + 30, '8 to 30 Days',

          If([Due Date] > Today()+ 90 and < Today() +180, '90 to 180 days' as Segments

Make all the segments.  Then in your chart make an expression for all you segments like this:

Sum({$<Segments={'8 Days'}>} Amount)

Make your dimension issuing bank and this will give you the second chart.