Webi three year reporting

[former_member602839]

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)))

[former_member602839]

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])

[sven_oehme]

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