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]

Not sure what is not working, did you check the QVW document I posted? May be if you look at that you may find what isn't working for you.

[maahivee]

Sunny - What is happening is that the date format is not changed even if i give it as date(date) as date

in the script, it is still showing in the number format, i think that is where the problem is coming.

is it possible to use some kind of function like "makedate" in the expression itself instead of script??

[sunny_talwar]

See if this helps:

Date(Num#(Date, '##')) as Date //Use the format Date is in. For instance if number has a comma use #,##0

I hope this will help

Best,

Sunny

[maahivee]

Sorry i dint get you.

[sunny_talwar]

Is Date like 41829 or 41,829??

If its the first one, try doing this:

Date(Num#(Date, '##')) as Date

See if it changes Date from Number to Date.

[maahivee]

Sunny- Thanks so much for your patience, I have tried the above way,but still it dint change the date from number format. But is there anything we can do with the front end expression instead of the back end script??

[sunny_talwar]

It's working now???

[maahivee]

No, it is not, That is why i am asking if there is anything to do in the front end.

[sunny_talwar]

It is possible:

See if this works:

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

or

Sum({<Date = {">= $(=Num(AddYears(Today(), -1))) <= $(=Num(AddMonths(AddYears(Today(), -1), 5)))"}>}Sales)

[maahivee]

Perfect- This helped me so much, thank you so much, for all you patience too.