Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to count Customers are who made sale twice in the last three months.
I am using below expression to calculate Sales for the same.
But it is not working.
Please help me.
Thanks in advance.
=Sum(Aggr(If(Count({<{Year=, Month, Quarter=, Date={'>=$(=MonthStart(Today(), -2))<=$(=MonthEnd(Today()))'}>} TransactionID) > 1, 1), Customer))
Try this:
=Sum(Aggr(If(Count({<Year=, Month =, Quarter=, Date={"$(='>=' & MonthStart(Today(), -2) & '<=' & MonthEnd(Today()))"}>} TransactionID) > 1, 1), Customer))
Your sample attached doesn't have dates from 2015. Everything is 2014 and this seems to work for 2014:
=Sum(Aggr(If(Count({<Year=, Month =, Quarter=, Date={"$(='>=' & MonthStart(AddYears(Today(), -1), -2) & '<=' & MonthEnd(AddYears(Today(), -1)))"}>} TransactionID) > 1, 1), Customer))
Your expression might also work if you have dates from 2015 in your original data, but you just need to remove the extra curly ({) bracket
=Sum(Aggr(If(Count({<{Year=, Month, Quarter=, Date={'>=$(=MonthStart(Today(), -2))<=$(=MonthEnd(Today()))'}>} TransactionID) > 1, 1), Customer))
Hi sunindia,
I changed the data to 2015. But still it is not working. Please help me.
Thanks in advance.
Try this:
=Sum(Aggr(If(Count({<Year=, Month =, Quarter=, Date={"$(='>=' & MonthStart(Today(), -2) & '<=' & MonthEnd(Today()))"}>} TransactionID) > 1, 1), Customer))
Hi sunindia,
Thanks for the solution.
Why should be use TransactionID) > 1 in expression,please.
=Sum(Aggr(If(Count({<Year=, Month =, Quarter=, Date={"$(='>=' & MonthStart(Today(), -2) & '<=' & MonthEnd(Today()))"}>} TransactionID) > 1, 1), Customer))
That is the expression you provided, I just worked with the set analysis part. I am not sure what exactly you are trying to do there.
Hi,
You can try this expression, for counting the customers who is having more that one TransactionID in the past 3 months..
=Sum(Aggr(If(Count({<Year=, Month =, Quarter=, Date={">=$(=MonthStart(Today(), -2))<=$(=MonthEnd(Today()))"}>} TransactionID) > 1, 1), Customer))
Hope this helps you.
Regards,
Jagan.
Hi jagan,
Thank you so much for the solution. It is working fine.
I came to know different solutions for same results.