Set Expression for leading and trailing months

[maahivee]

Hello Friends,

I have a tricky requirement: I have to show the sum(sales) for in 2 cases;

1st case is Leading 5 months: In this i have to show the sum(sales) for last year this date forward to 5 months,

That means say today's date is 6/23/2015, my expression should be for 6/23/2014 to   11/23/2014 which is 5 months from today but last year.

2nd case is Trailing 12 months: this is just the sum(sales) for last 12 months i.e 6/23/2015 to 6/23/2014.

I tried plenty different ways but no luck on anything, i have tried like creating variables and using them in expressions and all those kinds of things in  set expressions. Also keeping in mind i am not an expert at writing the set expressions.

That would be really great if you guys can help me out on this one.

[sunny_talwar · Accepted solution]

Try these:

1) {<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY'))"}>}

2) {<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(Today(), 'M/DD/YYYY'))"}>}

These should help.

Best,

Sunny

[maahivee]

Hello Sunindia, Thanks a ton for the response, But if i am using those expressions it is just giving zeros all over, can you please help me.

[sunny_talwar]

Lets start from here:

What do you get when you paste ='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY') in a text box? are the dates in the same format as your date field???

[maahivee]

Thanks for the quick reply.. The above exp if i put it in a text box is giving a value..

>=6/24/2014<=11/24/2014

[sunny_talwar]

Is this the same format you have Date in? (The left hand side of the equation below)

{<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY'))"}>}

[maahivee]

Yes, the date is in the same format, MM/DD/YYYY.

[sunny_talwar]

Can you show your complete expression and some screenshots of data where you expect this expression to sum/count/min/max the data???

[maahivee]

I am trying to so like a sum with the expression you gave me like this

Sum({<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'M/DD/YYYY'))"}>}Sales)

But this is the result i am getting out of both the expressions.

[sunny_talwar]

Not sure why it isn't working, but I guess see if this makes any difference:

Sum({<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'MM/DD/YYYY') & '<=' & Date(AddMonths(AddYears(Today(), -1), 5), 'MM/DD/YYYY'))"}>}Sales)

If this doesn't work, would you be able to share a sample?