8 Replies Latest reply: Mar 16, 2018 12:24 PM by Digvijay Singh RSS

    Frequency Based of

    Raman Raghav

      Hi All,

      I was stuck in a calculation to get frequency based of . The requirement is , over the period of 24 months in how many months the supplier is active within 1-12 month and 13-24 months. For example if a Supplier is active in more than 6 months in 1-12 months period and 13-24 months period then it should show Recurring likewise there are 3 other conditions which is explained in the excel sheet attached

      Following Dimensions are to be used:

      1)[Invoice ID]

      2)[Paid Month]

      3)[Paid Date]

       

      I tried the below calculation but its wrong when I validate the data

      if (count( {< paid_date = {"$(='>=' & Date(AddMonths(Max(paid_date), -24)) & '<=' & Date(AddMonths(Max(paid_date), -12)))"},

      [Paid Month] = {"=count([Invoice ID])>0"} >} Distinct [Paid Month] ) > 6,

       

      if(count( {< paid_date = {"$(='>=' & Date(AddMonths(Max(paid_date), -12)) & '<=' & Date(AddMonths(Max(paid_date), 0)))"},

      [Paid Month] = {"=count([Invoice ID])>0"} >} Distinct [Paid Month] ) > 6,'New','Recurring'),'Year over Year')

       

      TIA