Help with expression

[Anonymous]

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

[qlikmsg4u · Accepted solution]

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

[rubenmarin1]

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

[PrashantSangle]

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

[ankit777]

Try this

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

[Anonymous]

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

Thanks for your help everyone