Year-on-Year Variance

[#1]

Hi All,

I have the following sample data:

Mall, Shop, Year, Month, Category, Rental
MallA, Shop1, 2008, 01, Fashion, 100
MallA, Shop1, 2008, 02, Fashion, 150
MallA, Shop1, 2009, 01, Fashion, 200
MallA, Shop2, 2009, 01, Books, 220
MallA, Shop3, 2009, 01, F&B, 180
MallA, Shop4, 2008, 01, IT, 300

I need to calculate a Rental year-on-year variance in the report with Year Month as the dimension column and a cyclic group consist of Mall and Category.

Required Result: By Mall

Mall A : 200901 Year-on-Year Variance
= sum of Rental for 200901 / sum of Rental for 200801 -1
= (220+180+200) / (100+300) - 1
= 50 %

Required Result: By Category

Category Fashion : 200901 Year-on-Year Variance
= sum of Rental for 200901 / sum of Rental for 200801 -1
= (200) / (100) - 1
= 100 %

Please help. I tried set analysis but no idea how i can get last year's same month on this year's same month column.

I attach a sample QV file. Thanks a lot!

[qliksus]

Hi its quite a tricky task i have modified ur file and attached .Please check the result and revert back .u need to create a dimension isolated from ur existing cloud and then u can do it .go through the file and try to understand