topic Re: Last 3 months Expression is not working in QlikView

Last 3 months Expression is not working

qlikviewwizard — Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Last 3 months Expression is not working

sunny_talwar — Mon, 06 Jul 2015 03:01:00 GMT

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

Re: Last 3 months Expression is not working

sunny_talwar — Mon, 06 Jul 2015 03:08:49 GMT

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

Re: Last 3 months Expression is not working

qlikviewwizard — Mon, 06 Jul 2015 03:26:52 GMT

Hi sunindia,

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

Thanks in advance.

Re: Last 3 months Expression is not working

sunny_talwar — Mon, 06 Jul 2015 03:29:01 GMT

Try this:

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

Re: Last 3 months Expression is not working

qlikviewwizard — Mon, 06 Jul 2015 03:32:35 GMT

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

Re: Last 3 months Expression is not working

sunny_talwar — Mon, 06 Jul 2015 03:33:52 GMT

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.

Re: Last 3 months Expression is not working

jagan — Mon, 06 Jul 2015 03:51:06 GMT

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.

Re: Last 3 months Expression is not working

qlikviewwizard — Mon, 06 Jul 2015 04:27:51 GMT

Hi jagan,

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

I came to know different solutions for same results.