Help with Expression with Dates

[rustyfishbones]

Hi All,

I have the following expression

NUM(Sum ({<[TrxType]={'R'},ActionID ={'RECEIPTS'},Year = {$(=MAX(Year))}, Month = {'$(vPriorMonth)'},Day = {"<=$(=DAY(MAX(Date)))"}>}[#Act-Prime]),'#,##0')

when I view it using a straight table it return the correct details

But it's returning no Value

It should return 2945 which is the 01/05/2017

[sunny_talwar · Accepted solution]

What is the exact expression used in the list box object? Also, how do you define Month field in the script and how do you define vPriorMonth?

[rustyfishbones]

SUM([#Act-Prime]) is used in List Box

Month:

MONTH(Date) as Date

vPriorMonth:

=MONTH(ADDMONTHS(MAX(Date), -1))

[rustyfishbones]

I see the problem now.

No  [#Act-Prime] for TrxType = {'R'} for that Date

[rustyfishbones]

So the expression is correct

[sunny_talwar]

Try this as it seems that you only have start of month in your date column

Sum({<Date = {"$(=Date(MonthStart(Max(Date), -1), 'DD/MM/YYYY'))"}>}[#Act-Prime])

Not sure I understand the purpose of this as these are not in list box object and I wouldn't want to add them in the other expression to make sure that the number match up

[TrxType]={'R'},ActionID ={'RECEIPTS'}

[rustyfishbones]

Hi Sunny,

I am trying to compare this month to date v Last Month to Date

If if today is 14/07/2017, for this month I want everything from 01/07-14/07

and Last Month should be 01/06-14/06

It's the Last Month that I have issues with

[sunny_talwar]

For that, I would try this may be

Last Month

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Max(Date), -1), 'DD/MM/YYYY'))"}, Month, Year>}[#Act-Prime])

This Month

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Max(Date), 0), 'DD/MM/YYYY'))"}, Month, Year>}[#Act-Prime])

[rustyfishbones]

That's it thanks alot

[sunny_talwar]

No problem at all