Question Re: Webi three year reporting in Technology Q&A

Webi three year reporting

Former Member — Sat, 09 Feb 2019 10:45:26 GMT

I have a requirement for year on year reporting on webi 4.2 sp5.

I have a Spend key figure, Date dimension and other dimensions to slice the data.

My problem currently is extracting individual years from the date so that I can do year on year comparison.

What I need is

1. Sum(If([Posting Year]=Max(Right([Posting Date];4));[BEE Spend];0)) -- Working fine so far

2. Sum(If([Posting Year]=FormatDate(RelativeDate(ToDate(Max(Right([Posting Date];4));"yyyy");-1;YearPeriod);"yyyy");[BEE Spend];0)) In([Supplier];[Posting Date]) -- Please help this one is not working it returns #Multivalue Error.

I have created a Year variable called [Posting Year] as (Right([Posting Date];4)))

Re: Webi three year reporting

sven_oehme — Sat, 09 Feb 2019 22:27:18 GMT

Hey Thato,

looks quit complicated. Why didn't you use a dimensional table-view over the years with FormatDate(Right([Posting Date];4);"yyyy") (put it in a variable f.e.) and just sum the key figure like: Sum([BEE Spend]) In([Supplier];[Posting Date]) . At the end you are able to reduce the number of years of filtering.

Grtz

Sven

Re: Webi three year reporting

Former Member — Sun, 10 Feb 2019 10:05:23 GMT

Hi Sven,

thanks for responding, I tried your suggestion but still no luck. Also my main problem is getting previous year spend.

Sum([BEE Spend]) Where([Date Variable] = [Previous Year]) In([Supplier];[Posting Date])