Can you check this expression please?

[#1]

Hello All,

Unable to figure out if there is something wrong in this expression here:

=sum({$<TIME_PERIOD_WEEK_NO = "Week(Today())-52">}WK_SALES_GROSS_REV)

I am trying to calculate the rolling weekly sales amount. So I want to do a Current week - 51 weeks at any point of time on a chart. I already posted, but could not get any reply for this. I am sure someone from you might have done this before I am currently in week 9 of 2011 and I want to show the data for this 9 weeks till the week 10 of 2010.

Please help.

[Miguel_Angel_Baeyens]

Hello,

Something like the following might work

sum({$<TIME_PERIOD_WEEK_NO = {'>=$(=Week(AddYears(Today(), -1)))'} >}WK_SALES_GROSS_REV)

Anyway, it's always better to work with dates, since as a year has 52 weeks, you lck of any other field such a year to identify which dates you want to aggregate. So instead something like

sum({$< DATE = {'>=$(=AddYears(Today(), -1))'} >} WK_SALES_GROSS_REV)

having Week as dimension should work as well.

Hope that helps.

[#3]

Hello Miguel,

Thank you for the quick reply, I was playing around with the expressions as suggested by you. When I use the first expression as suggested by you, it shows the fixed 52 weeks data on the chart and doesnt change when I click on Year 2011. I was expecting to change it from week 10 2010 to week 9 2011 when I click on 2011. I also tried your second expression as suggested by using the Year as below, and this doesnt work at all.

=sum({$< TIME_PERIOD_YEAR = {'>=$(=AddYears(Today(), -1))'}>} WK_SALES_GROSS_REV)

Any further help please?

Thanks in advance,

[Miguel_Angel_Baeyens]

Hi,

AddYears() doesn't return a year, but a date. So you have to use your Date field instead your Year field.

Regards.

[#5]

Hello Miguel,

I have the Dimension called TIME_PERIOD_START_DATE in which I have 12 periods and the dates in the format of YYYY-MM-DD hh:mm:ss and I tried to use it, can you check this and let me know whats wrong. The problem is I dont want to select any week, but the chart should show the fixed data for Current Week - 51 weeks at any point of time(rolling weeks).Please help me with this.

=sum({< TIME_PERIOD_START_DATE = {'>=$(=TIME_PERIOD_START_DATE(Today(), -1))'}>} INV_NET_REV)/1000

[Miguel_Angel_Baeyens]

Hello,

Check with something like the following:

sum({< TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'}>} INV_NET_REV)/1000

TIME_PERIOD_START_DATE is not a function, so it doesn't accept parameters. I've used here AddYears, because you want the last 52 wweks (that is one year ago).

Hope this helps.

[#7]

Well Miguel,

The suggestion you made looks like working, but I am not working with full data so cannot validate that yet. However my question is, Its showing the last 52 weeks data but I want that from Today - last 52 weeks and when we do add years, will that still consider the current year 10 weeks and add the rest of the 42 weeks from previous year? As thats my requirement.

Thanks for your time and help.

Andy

[Miguel_Angel_Baeyens]

Hello Andy,

That expression will Sum everything from one year ago on. So for today, 03/03/2010, for tomorrow, 04/03/2010, the day after tomorrow, 05/03/2010, and so on. If this expression is in a chart where the dimension is Week, it will display the information per weeks, taking into account the current one 10-2011, from 11-2010 last year. The next week, it will display from 12-2010 to 11-2011, and so on.

Hope that helps.

[#9]

Hello Miguel,

I was trying this query I posted sometime back as I started working with the Previous year data now. When I used the above expression as mentioned by you, it doesnt seem to work? I have a Time dimension field for TIMEYearWeek which is in the format of 201101 and I have another dimension like TIME_PERIOD_WEEK_NO which is just a number from 1-52 for any year selected and I have a TIME_PERIOD_START_DATE which is like YYYY-MM-DD hh:mm:ss dimension whcih actually holds the data in Time stamp format for a period whenever started..

Can you please help me build a chart which always shows rolling current - 52 weeks data?

Thanks a Ton,

ANDY

[#10]

Hello Miguel,

It looks like its working, however please look at this expression below, the problem is I have the Forecast data for the whole year 2011 and the chart is obviously showing the line for the whole year after 52 weeks calculation, so was wondering how do i fix it only to show till the current week just like the sales data??

=sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'},FORECAST_TYPE= {'Q0F'}>}FCT_GROSS_REVENUE)/1000

Thanks a Ton,

ANDY