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?

[PrashantSangle]

Hi,

In Your Date()

You use format 'dd/mm/yyyy'

in this mm---->minutes

not month

So use Format like 'DD/MM/YYYY'

MM--->Month

Regards

[jagan]

Hi,

Check whether the ARPaymentMonth and vEnd3MonthsBack is in this format "dd/mm/yyyy" if not convert to the same format.  If possible attach a sample file.

Regards,

Jagan.

[#4]

Hi Jagan

I have rechecked all formats are correct. To recap:

Script now includes:

let vEnd3MonthsBack = Date(monthend(addmonths(date(today()),-3)),DD/MM/YYYY);

Data Load now includes:

date(monthend(paymentDate),'DD/MM/YYYY') as ARPaymentMonth,

The table format is:

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

… this returns ALL data to the table – not just the required items.

[#5]

I have rechecked this with all date formats as DD/MM/YYYY … same result …

[PrashantSangle]

Hi,

I am just go through all your expression you are not using ''(Single Quotes) to format your date,

Try this If you are using variable

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

and also check for  Other expressions also .

Regards,

[#7 · Accepted solution]

Hi,

Use this to create variable

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

Expression:

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

It will work...

[#8]

Thanks for your help, everyone  The correct solution was:

In the script:

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

In the chart table:

SUM({<ARPaymentMonth={'$(vEnd4MonthsBack)'}>}ARInvoiceOutstandingAmount)

(Ravi - your solution had a '}' missing, but I resolved that)