7 Replies Latest reply: Mar 5, 2013 12:45 AM by Some Nath Roy RSS

    Split accounts receivable in time periods

    Thorsten Schröder

      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.