Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
stalwar1@ Any help would be Appreciated
I would look at using the Aggr function
digvijay Can you please help me
Thank you
Hi Bill,
Would you mind to send me the complete expression
Thank you.
Can you share some sample to try on?
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
the calculatio works absolutely fine
thank you so much.
Its always better to close the thread if you feel your queries are resolved. Thanks