Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Split accounts receivable in time periods

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+

ar.PNG

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.

1 Solution

Accepted Solutions
somenathroy
Contributor III

Re: Split accounts receivable in time periods

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

7 Replies
somenathroy
Contributor III

Re: Split accounts receivable in time periods

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

Not applicable

Re: Split accounts receivable in time periods

Hi

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

Not applicable

Re: Split accounts receivable in time periods

@altafamber the document is empty

Not applicable

Re: Split accounts receivable in time periods

please check now

Not applicable

Re: Split accounts receivable in time periods

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

Not applicable

Re: Split accounts receivable in time periods

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
Contributor III

Re: Split accounts receivable in time periods

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

Community Browser