Please see the attached image. In the table I’ve created [Tenancy ID] and [Period End Date] are dimensions brough back from my universe together with a measure of [V Balance].
[Start Date] is currently a measure because it is:
=Min([Period End Date]) In ([Tenancy ID (Tenancy History)])
This forces it to show the earliest date a Tenancy ID has had a balance with us.
I’ve then created a variable that for each and every Tenancy ID ( and I have over 10,000) calls the earliest week, Week 0 and then increments the number up by one per period. So;
Week Number
=Truncate(DaysBetween(ToDate([Start Date]
;“dd/MM/yyyy”) ;[Period End Date])/7 ;0)
I have managed to get Webi (XI R2) to save this variable as a dimension instead of a measure.
So, what’s my problem!? I can’t make [Start Date] into a dimension in the report because it uses Min() and so has to be a measure. When I remove [Period End Date] and [Start Date] from my table, my contrived [Week Number] becomes a Multivalue error.
I think that a way round this is for me to alter [Week Number] to be relative to Each [Period End Date], but that doesn’t seem to work. = Any ideas?!
At the end of the day, I would like to just have a cross tab table with all my tenant ids in the left column, Week number in the top row and balance in the data.
Thanks Lauren, I see where you’re coming from & have tried that previously in similar scenario, just tried in this one again & it doesn’t work.
I think that this is a little bit of a limitation of webi’s but I’m more than happy to be proved wrong. As per my topic subject, I think that my problem could be solved by correctly referencing the context within which my resulting week number sits.
I’ve been attempting to work with the foreach() statement for most of today trying to get a table with tenancy ID, week number and balance in it, and that data alone - and I just can’t do it. This definitely works in deski, I’ve used my contrived week number formula extensively in numerous report suites before to create graphs and tables in reports previously with no problems.
Just an idea, if you can get this to work in Deski, you could evaluate the variable, in its own context?
You could then consider trying to convert the Deski report to Webi, if you have access to the conversion tool, whether this would work is debatable .
Or you could see what the, full, calculation context Deski uses and try to convert, the syntax, to Webi?
Tried & done . Spent too much time in webi trying to get it to work I didn’t think of that.
Was a little fiddly to get the contexts right to say the least in webi (I did option b & hovered the mouse over the formula in deski to see the full context) but it now works & I have an excellent report.
I’m in webi xi r2 - is it possible to view the syntax in webi r3 as we can in deski? It’s a proper pain in the bottom not having it now for precisely this kind of reason!
I moved on since I wrote the original post. As the posts say though, I looked at the context the formula was in in Deski and applied that context to the formula in Webi to resolve it.
(BOB member since 2009-11-02)
(BOB member since 2009-09-15)
(BOB member since 2009-11-02)
.
(BOB member since 2005-01-06)
. Spent too much time in webi trying to get it to work I didn’t think of that.
.