topic Re: Help with expression in QlikView

Help with expression

Anonymous — Tue, 09 Jun 2015 11:40:04 GMT

Hi guys,

I have an expression

=count({$<[Invoice Date] = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( MonthEnd (Today()),-12))'},invoice_value={"=invoice_value>0"}>}Distinct left(invoice_CustNo,5))

However this is doing a count up until the end of the month 2014. Can this be changed somehow to only show up until the same day of the current month but previous year? e.g. 09/06/2014

Hope this makes sense

Re: Help with expression

qlikmsg4u — Tue, 09 Jun 2015 11:44:57 GMT

Hi Jamel,

make it

=count({$<[Invoice Date] = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( Today(),-12))'},invoice_value={"=invoice_value>0"}>}Distinct left(invoice_CustNo,5))

remove Monthend function

Re: Help with expression

rubenmarin — Tue, 09 Jun 2015 11:46:11 GMT

Or you can use Addyears() fundtion:

=count({$<[Invoice Date] = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddYears(Today(),-1))'},invoice_value={"=invoice_value>0"}>}Distinct left(invoice_CustNo,5))

Re: Help with expression

PrashantSangle — Tue, 09 Jun 2015 11:47:02 GMT

Hi,

Just remove MonthEnd()

Try below

=count({$<[Invoice Date] = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( Today(),-12))'},invoice_value={"=invoice_value>0"}>}Distinct left(invoice_CustNo,5))

Regards

Re: Help with expression

ankit777 — Tue, 09 Jun 2015 11:47:52 GMT

Try this

=count({$<[Invoice Date] = {'>=$(=AddMonths( Today(),-12))'},invoice_value={">0"}>}Distinct left(invoice_CustNo,5))

Re: Help with expression

Anonymous — Tue, 09 Jun 2015 11:49:18 GMT

Ah didn't realise there was a "MonthEnd" text slipped in there!

Thanks for your help everyone