Last 3 months Expression is not working

[qlikviewwizard]

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

[sunny_talwar]

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

[sunny_talwar]

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

[qlikviewwizard]

Hi sunindia,

I changed the data to 2015. But still it is not working. Please help me.

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this:

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

[qlikviewwizard]

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

[sunny_talwar]

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.

[jagan]

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.

[qlikviewwizard]

Hi jagan,

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

I came to know different solutions for same results.