Using Set Analysis with Date Variable

[#1]

I have a simple problem and can't see why this is not working.  I need to display values in table columns based on when payment is expected.  I ma using the following Set Analysis:

sum({$<ARPaymentMonth={'=$(=Date(vEnd3MonthsBack,dd/mm/yyyy))'}>} ARInvoiceOutstandingAmount)

What is happening is ALL values are being returned, rather than just ones for the selected month.  Any ideas?

[sushil353]

Hi,

Try this one:

sum({$<ARPaymentMonth={'=$(=Date('$(vEnd3MonthsBack)',dd/mm/yyyy))'}>} ARInvoiceOutstandingAmount)

HTH

Sushil

[#3]

hi

try this

sum({$<ARPaymentMonth={'=$(=Date('=$(vEnd3MonthsBack)',dd/mm/yyyy))'}>} ARInvoiceOutstandingAmount)

[#4]

Thanks for your response, however there appears to be a problem with this syntax:

[#5]

Try this

Create a variable for the expression

vARPMonth = Month(Date(vEnd3MonthsBack,dd/mm/yyyy))

then

SUM({<ARPaymentMonth={$(vARPMonth)}>}ARInvoiceOutstandingAmount)

or

sum({$<ARPaymentMonth={'=$(=Month(Date(vEnd3MonthsBack,dd/mm/yyyy)))'}>} ARInvoiceOutstandingAmount)

[jagan]

Hi,

Try like this, Outer and Inner are single quotes, because of this you are getting the error

sum({$<ARPaymentMonth={"=$(=Date('$(vEnd3MonthsBack)',dd/mm/yyyy))"}>} ARInvoiceOutstandingAmount)

Regards,

Jagan.

[#7]

I have tried this syntax as suggested … but still get the full dataset of values returned.

[#8]

I tried both options as suggested, but with this I get a zero value back. Here is the exact syntax I tried:

In the script:

let vEnd3MonthsBack = Date(monthend(addmonths(date(today()),-3)),dd/mm/yyyy);

In the chart table:

SUM({<ARPaymentMonth={$(vEnd3MonthsBack)>}ARInvoiceOutstandingAmount)

[PrashantSangle]

Hi,

I think in your table

ARPaymentMonth=contain Month

and expression you are using contain

Date('$(vEnd3MonthsBack)',dd/mm/yyyy) Which return Date

So that you have to use Month() or Monthname() before above expression to match with your fieldname

Try this

If ARPaymentMonth contain only jan,feb, etc than use,

sum({$<ARPaymentMonth={'=$(=Month(Date(vEnd3MonthsBack)))'}>}

OR

sum({$<ARPaymentMonth={'=$(=Month(Date(vEnd3MonthsBack,'DD/MM/YYYY')))'}>}

IF ARPaymentMonth contain only jan 2013,feb 2013, etc than use

sum({$<ARPaymentMonth={'=$(=MonthName(Date(vEnd3MonthsBack)))'}>}

Or

sum({$<ARPaymentMonth={'=$(=MonthName(Date(vEnd3MonthsBack,'DD/MM/YYYY')))'}>}

Regards,

[#10]

I have rechecked the data load – this is how it is formatted (I added the date function to see if it made a difference):

date(monthend(paymentDate),'dd/mm/yyyy') as ARPaymentMonth,

The script includes:

let vEnd3MonthsBack = Date(monthend(addmonths(date(today()),-3)),dd/mm/yyyy);

The table format is:

SUM({<ARPaymentMonth={$(vEnd3MonthsBack)>}ARInvoiceOutstandingAmount)

… and as mentioned, this loads ALL data values …