Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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])
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])
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.
I think it will need to be played around with a little bit. Would you be able to share a sample?
Hope this is ok.
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])
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.
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])
You have been most helpful! Thank you for your time, you saved me a lot of head scratching .
Have a great weekend!
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.