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

# Frequency Based of

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

• ###### Re: Frequency Based of

stalwar1@ Any help would be Appreciated

• ###### Re: Frequency Based of

I would look at using the Aggr  function

• ###### Re: Frequency Based of

Hi Bill,

Would you mind to  send me the complete expression

Thank you.

• ###### Re: Frequency Based of

digvijay Can you please help me

Thank you

• ###### Re: Frequency Based of

Can you share some sample to try on?

• ###### Re: Frequency Based of

As discussed over the call please correct the formula. Please close this thread in case you don't have further questions on this specific problem. Thanks

• ###### Re: Frequency Based of

the calculatio works absolutely fine

thank you so much.

• ###### Re: Frequency Based of

Its always better to close the thread if you feel your queries are resolved. Thanks