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.

[Anonymous]

Hi Can you use trellis chart on date function

[maahivee]

sunindia- That is the same thing i am trying but it is not working.. . what do you exactly mean by sample??

[sunny_talwar]

May be like 5-10 lines of data from your database relevant to the chart that you are trying to make. Problem is if I mock up some data it might work in there and still won't work for you. So the it would be best if we use your data to start with.

[maahivee]

i have dumped some data to excel but how can i attach it here??

[sunny_talwar]

See the two screenshots below:

[maahivee]

Sunindia- I have attached the file here, Also while doing that i have found that the original date is not in the mm/dd/yyyy format instead in the number format. i thought it is in the date format since i forgot that i changed the format of it.

[sunny_talwar]

Change your date in the script like this:

Table:

LOAD [Item Number],

    Date(Date) as Date,

    Sales

FROM

Community_169701.xlsx

(ooxml, embedded labels, table is TB02_20150624_101755);

and then try the expressions I gave your.

I am also attaching a sample qvw for reference,

HTH

Best,

Sunny

[maahivee]

Sunny- I have tried it in both of these ways..

date(date) as date  and also in

date(date,'mm/dd/yyyy') as date

but unfortunately both dint work in my case.

[sunny_talwar]

Have you made an selection when you are seeing the output? Clear all selections so that we can check if it is because of a selection or is the expression not working at all

[maahivee]

Yes- i definitely cleared all the selections.