I’m trying to write a report, where I get back from the query 104 weeks of Sales Data. What I’m then trying to do is create a variable that gives me for each of the first 52 weeks, a sum of the previous 52 weeks to display in a graph. Eg
Week 1 = (sum from Week 1 to week 52)
Week 2 = (sum from Week 2 to Week 53)
Week 3 = (sum from Week 3 to Week 54)
and so on until
Week 52 = (sum from Week 52 to Week 103)
I can’t seem to work out how to do it. I’ve tried several things like previous()…but I’d need 52 previous statements…and then if I filtered the block down to the last 52 weeks, the oldest weeks would not have the full 52 weeks in them
I’ve tried to play with context, but can’t seem to get my head around how to do this.
Do you have access to the universe? If so, you may find that your database has functionality similar to Previous() which can be customised to go back n levels, where n would be 52 in your case. The example I always quote is the lag function in Oracle.
I would actually recommend that you go back even further than that. Talk to your DBA about building a view for your in the database itself. You can build this sort of thing with subqueries much more easily than you can with repeated Previous() functions.
I’m thinking that there ought to be a way to do with scopes in WebI, but I would have to do a lot of experimentation to figure it out.
Yes, I do have access to the universe, and thought I could build something that had a join between my fact and date tables to give me the result, but the only way I could think to do it was inefficient as I’d have 52 hits at the fact table to get the 52 x 52 week information.
I’ve looked at the ‘lag’ function in Oracle, and this seems interesting, only it appears from what I can see just to get the last row, and my query will have other dimensions (product, customer, region etc) and so the order of the rows may be an issue…I’d need to ensure it got the last 52 weeks, not the last 52 products etc.
I’ll keep hacking away at a universe / report change. If you have any other suggestions it would be greatly appreciated.
(BOB member since 2006-10-06)
(BOB member since 2002-08-15)
(BOB member since 2007-12-11)
(BOB member since 2003-11-27)