Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi
Please fint the attached file hopefuly it will give you solution...
@altafamber the document is empty
please check now
Here's a sample of how I want to split the date 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 ...
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