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

    Debtors Overdue History:- Set Analysis & AsOfWeek Issue

    Robert Hutchings

      Hi

       

      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 ,

      ARValue))

       

      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

       

      Thanks