Split accounts receivable in time periods

[#1]

Hello,

I already posted "this" question in the Newbie forum (http://community.qlik.com/message/317144#317144) but I think I worded it too confusingly so please try this post first

I want to split the days of payments for accounts receivable. In the QlikView demo "Executive Dashboard" there is such a straight table with days split into certain time periods like: 1-30d; 31-60d; 60+

In the demo the table itself already contains the split up columns with the calculated values.

In my table I have a Maturity- and Cutoff- Date. How can I use those two dates to split up the days just like in the demo?

I have tried several ways:

For example, in the load stmt, I put:

if (Num(Date([Maturity]))-Num(Date([CutoffDate])) >=-30,'<=30d') As '<=30d',
if (Num(Date([Maturity]))-Num(Date([CutoffDate])) >=-90,'30d><=90d') As '30d><=90d',

and so on. I couldn't figure out how to sum my Amount column in there.

I also tried to combine the above if stmts in one column "myDueInfo".

Then in my straight table I put several expressions:

if (myDueInfo LIKE '<=30d',sum(Amount)) , 0)

but this of course only works when I select the '<=30d' field. But what I really want is to show the values for all day periods without any selection needed. Again, just like in the demo.

I think my setup needs to change, like In the demo, where the fields already contain the calculated values.

I hope you can help me with this.

thank you.

[somenathroy]

Hi,

You may try like below code with LOAD statement in script:

Sum(if (Date([Maturity])-Date([CutoffDate]) >=-30, Amount,0)) As '<=30d',

Sum(if (Date([Maturity])-Date([CutoffDate]) >=-90,Amount,0)) As '30d><=90d',

And in expression use Sum([<=30d]) under label <=30d.

Regards,

Som

[#3]

Hi

Please fint the attached file hopefuly it will give you solution...

[#4]

@altafamber the document is empty

[#5]

please check now

[#6]

Here's a sample of how I want to split the date periods.

[#7]

Hi somenathroy,

I get an error when I insert the statement:

Sum(if (Date([Maturity])-Date([CutoffDate]) >=-30, Amount,0)) As '<=30d'

I put it beneath the last Column making the Sum stmt the new last column.

This error occurs:

OLEDB read failed

SQL SELECT *

from ...

[somenathroy · Accepted solution]

Sorry I could not able to open your document. Anyway I think the approach should be as below:

first extraction done without any aggregation (e.g sum()) function:

QV_SQL:

SQL SELECT *

from ...

Next do the necessary aggregation and/or transformation over the above resident table like:

QV_Aggred:

LOAD *,

Sum(if (Date([Maturity])-Date([CutoffDate]) >=-30, Amount,0)) As '<=30d',

Sum(if (Date([Maturity])-Date([CutoffDate]) >=-90,Amount,0)) As '30d><=90d'

Resident QV_SQL

Group By ......;

Regards,

Som