Announcements
cancel
Showing results for
Did you mean:
Master II

Last 3 months Expression is not working

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.

=Sum(Aggr(If(Count({<{Year=, Month, Quarter=, Date={'>=\$(=MonthStart(Today(), -2))<=\$(=MonthEnd(Today()))'}>} TransactionID) > 1, 1), Customer))

1 Solution

Accepted Solutions
MVP

Try this:

=Sum(Aggr(If(Count({<Year=, Month =, Quarter=, Date={"\$(='>=' & MonthStart(Today(), -2) & '<=' & MonthEnd(Today()))"}>} TransactionID) > 1, 1), Customer))

8 Replies
MVP

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))

MVP

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))

Master II
Author

Hi sunindia,

MVP

Try this:

=Sum(Aggr(If(Count({<Year=, Month =, Quarter=, Date={"\$(='>=' & MonthStart(Today(), -2) & '<=' & MonthEnd(Today()))"}>} TransactionID) > 1, 1), Customer))

Master II
Author

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))

MVP

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.

Luminary Alumni

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.

Master II
Author

Hi jagan,

Thank you so much for the solution. It is working fine.

I came to know different solutions for same results.

Community Browser