Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, i have been trying over the past week to make a formula that would count distinct the customer field only when the order amount is bigger than 0 (if it's 0 it shouldn't count at all) and i need this for current year and current month.
This is as far as i got so far:
count(
{<Customer=
{"=Sum({<Year={$(=Year(Today()))}>} AND {<Month={$(=NUM(Month(Today())))}>} SUM(OrderAmount)) > 0"}, Year={$(=Year(Today()))}
,Month={$(=Num(Month(Today())))}
>}
DISTINCT Customer)
This only seems to work if i put year only or month only but when both conditions are there it just shows 0, i tried making avariable called YearMonthInt to show something like 201808 but that doesn't seem to work either.
Any advice would be appreciated.
Try this
Count({<Customer = {"=Sum({<Year = {$(=Year(Today()))}, Month ={$(=Num(Month(Today())))}>} OrderAmount) > 0"}, Year = {$(=Year(Today()))}, Month = {$(=Num(Month(Today())))}>} DISTINCT Customer)
Try this
Count({<Customer = {"=Sum({<Year = {$(=Year(Today()))}, Month ={$(=Num(Month(Today())))}>} OrderAmount) > 0"}, Year = {$(=Year(Today()))}, Month = {$(=Num(Month(Today())))}>} DISTINCT Customer)
This worked!!, thank you very much!