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
