set analysis formula

[#1]

can you use set analysis to look at only certain parts of a field?

For example, I have the calendar dates as 201438, 201439, etc... for forecasts but I also want to show sales.

what expression can I use to say only look at the sales for week 38 from year 2013...

[#2]

Can you post an excel output how it should show please?

Thanks

Suraj

[#3]

Check the first tab in the excel spreadsheet called 'final layout'

[#4]

Check this QVW. If this is what you are looking for.

Thank you

Suraj

[#5]

This is ok... Is there any other way to change the set analysis in the expression so that the year is not hardcoded?

Because in 2015, they would want to see 2014 and 2013... How could you change the set analysis so that it looks at the max year in the forecast and selects a year?

So if you had something like this, it would show as below

Week	Brand	Product	Retailer	Forecast	Sales 1 yr ago	sales 2 yr ago
201451	Low	B	LAX	100	150	200
201452	Low	B	LAX	200	250	300
201501	Low	B	LAX	300	350	400
201502	Low	B	LAX	400	450	450

the ones in bold would be from year 2013 and 2012.. while the underlined ones would be from 2014 and 2013...

[#6]

Check this.

Thanks

Suraj

[lucianosv]

You can do a LEFT JOIN using Table Forecast with itself in the script by keyword:

(Week - 100) the Week, Product, Brand and Retailer

[#8]

Suraj,

What if my sales dates were in the Monday date format... for example... 8/5/2013, 8/12/2013, 8/6/2012, 8/13/2012..etc but my forecast was still in format yyyyww (i.e 201437, 201428, etc)

How would this change my application?

[#9]

I would convert dates to their respective week. Like (Year(SalesDate) & week(SalesDate)).

Thanks

Suraj

[#10]

Luciano,

Would (Week-200) the Week, Product Brand and Retailer give me Sales from 2 years ago? or would I need to do something differently?

[#11]

(Week - 100) the Week, Product, Brand and Retailer

the top works for when you need it for a year before....what can I do to make get the actual sales for 2 years before?

If I do (Week-200), it's giving me the same results as the (week-100)

Is there some other set analysis that I could use?