Clients newer than 6 months with average monthly turnover of under 50$

[avantime]

Hi all,

I need help with a formula to show me all clients newer than 6 months that have an average monthly turnover of under 50$.

Right now I have:

=count({1}distinct {<[Client name]={"=sum(Turnover)<50"}, [Client add date]={"=[Client add date]>=date(monthstart(today(),-6))"}>} [Client name])

but obviously I don`t get the desired results as it shows me all clients newer than 6 months with a total turnover less than 50 in this period.

Thank you!

[sunny_talwar]

How about this:

=Count({1} DISTINCT {<[Client name]={"=Sum({<[Client add date] = {'>= & $(=Date(MonthStart(Today(),-6)))'}>}Turnover) < 50"}, [Client add date] = {'>= & $(=Date(MonthStart(Today(),-6)))'}>} [Client name])

[avantime]

Hi Sunny,

Thank you for your quick response!

There seems to be something wrong, it gives 0 as an result..

Last part of the code - = {'>= & $(=Date(MonthStart(Today(),-6)))'}>} - is black in the code editor.

[sunny_talwar]

I think it will need to be played around with a little bit. Would you be able to share a sample?

[avantime]

Hope this is ok.

[sunny_talwar]

Made a mistake while typing the expression out. This new one gives me a total count of 62

=Count({1} DISTINCT {<[Client name]={"=Sum({<[Client add date] = {'>=$(=Date(MonthStart(Today(),-6)))'}>}Turnover) < 50"}, [Client add date] = {'>=$(=Date(MonthStart(Today(),-6)))'}>} [Client name])

[avantime]

Thank you!

Can you please tell me why the count is affected by selecting invoice date? It should give the same figure, no matter what month is selected.

[sunny_talwar · Accepted solution]

Try this:

=Count(DISTINCT {1<[Client name]={"=Sum({1<[Client add date] = {'>=$(=Date(MonthStart(Today(),-6)))'}>}Turnover) < 50"}, [Client add date] = {'>=$(=Date(MonthStart(Today(),-6)))'}>} [Client name])

[avantime]

You have been most helpful! Thank you for your time, you saved me a lot of head scratching .

Have a great weekend!

[avantime]

Sorry for being such an annoyance, right now I saw that the formula shows all clients with total revenue under 50 $ and not an average monthly revenue of 50 $.

If I have a client that has a turnover of 50 $ in June and one of 50$ in July, it will not be shown.