Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master II
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.

Please help me.

Thanks in advance.

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

Capture.JPG

1 Solution

Accepted Solutions
Highlighted

Re: Last 3 months Expression is not working

Try this:

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

View solution in original post

8 Replies
Highlighted

Re: Last 3 months Expression is not working

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

Highlighted

Re: Last 3 months Expression is not working

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

Highlighted
Master II
Master II

Re: Last 3 months Expression is not working

Hi sunindia,

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

Thanks in advance.

Highlighted

Re: Last 3 months Expression is not working

Try this:

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

View solution in original post

Highlighted
Master II
Master II

Re: Last 3 months Expression is not working

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

Highlighted

Re: Last 3 months Expression is not working

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Last 3 months Expression is not working

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.



Highlighted
Master II
Master II

Re: Last 3 months Expression is not working

Hi jagan,

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

I came to know different solutions for same results.