1 Reply Latest reply: Sep 18, 2018 3:44 PM by Robert Hutchings RSS

    Debtors Overdue History:- Set Analysis & AsOfWeek Issue

    Robert Hutchings



      Hope someone can help on this


      The issue I'm having is comparing 30 60 90 etc DEBTOR days overdue over time. With just the unadjusted data I get a  nonsense figure due to the credits not allocated. I want to exclude the credit value by customer. For example 86417 credit compared too 130429 debit with the credits excluded


      The two measures were as follows


      AR Over 120 Days O/S

      sum({<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate}

      >} if( ARDueDate+121 <AsOfWeek ,



      Over 120 Alt   (alternative measure where negative values were excluded)

      sum({$<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate}

      ,CustAccNum = {"=sum({$<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate} >}if( ARDueDate+121 <AsOfWeek , ARValue))>0"}

      >}if( ARDueDate+121 <AsOfWeek , ARValue))


      Both these figures gave the correct value AS LONG AS ONE ASOFWEEK was selected


      As did other alt measures too


      The problem I have is the alt measure doesn't work with AsOfWeek as a dimension

      (unless I filter by AsofWeek 1/6/2018)


      The unadjusted figure is correct but the alternative isn't



      I think the issue is AsOfWeek doesn't work in set analysis as I've done it but I haven't been able to fix it yet (Ive tried lots of different options but non worked


      And help will be appreciation