topic Comparing and expression in another dimension. in QlikView

Comparing and expression in another dimension.

Fri, 12 Jul 2013 21:52:20 GMT

I am new to Qlikview and I'm trying to creat a comparison between two years for units and revenue. I'm sure it's pretty easy, but I'm still trying to learn the ins and outs of the software. I have 3 dimensions in a pivot table with two of them on the left (Category,Type) and one at the top (Year) with units and revenue for my expressions. I want to compare my units to the previous year as a number and as a percentage as well as comparing the revenue. Below is roughly how I want the pivot table to look. How would I create the comparison that I desire?

		2012		2013	Change from 2012	2013	Change from 2012
Category	Type	Units	Revenue	Units	Units	Revenue	Revenue
Lower	Mini	5	10	3	-2	6	-4
Upper	Single	10	30	15	5	45	15
Comp	Season	100	100	75	-25	75	-25
Mid	Group	150	300	100	-50	200	-100

Re: Comparing and expression in another dimension.

swuehl — Sat, 13 Jul 2013 00:08:37 GMT

QlikView will evaluate your expression for each dimension value, so if you have a year dimension with values 2012 and 2013, QV will use the same expression columns for both years. But to give you a start, try

=sum(Units)

[or count(Units), depending on how your data is defined, how you modelled Units]

=sum(Revenue)

=sum(Units)-before(sum(Units))

=sum(Revenue)-before(sum(Revenue))

before() is a chart inter record function (check the Help for more), that retrieves the expression value as evaluated in the dimension context to the left from the current dimension value.

Re: Comparing and expression in another dimension.

Mon, 15 Jul 2013 14:18:27 GMT

That worked... Easy enough. I thought it had something to do with the "before" function, but couldn't figure it out.

Next step... Is there a way to hide or get rid of one of the columns that don't have anything before it and therefore it will show 0s all the way down?

Also, is there way that I can get the units and revenue next to each other? This is similar to putting the values above a column header in an excel pivot table?