topic Calculated column on Horizontal Pivot in QlikView

Calculated column on Horizontal Pivot

rwunderlich — Sat, 15 May 2010 01:13:06 GMT

This one should be easy, I just can't find an example. I want to create a table like the following. Expressions on the vertical, one dimension (Year) on the horizontal. I want the Diff column to be the 2008 column - the 2007 column. I will have an arbitrary number of metrics (Budget, Sales) etc. I haven't been able to find an example that is a single table, but I beleve I've seen it done. Any ideas?

Year 2007 2008 Diff
Budget 800 2500 1700
Sales 1000 2000 1000

Thanks,
Rob

Calculated column on Horizontal Pivot

Sat, 15 May 2010 16:02:31 GMT

Hi Rob,

We can use the total field to display the diff by using rowno()=0 to identify the total column and set analysis to compare the dates.

Here is the expression I used for Budget...

if(rowno()=0, sum({$ <Year={$(=max(Year))}>} Budget) - sum({$ <Year={$(=max(Year)-1)}>} Budget), sum(Budget))

-Andy

Calculated column on Horizontal Pivot

Oleg_Troyansky — Sat, 15 May 2010 20:53:24 GMT

I was going to suggest synthetic dimensions, but the idea to use Total for this purpose is simply brilliant!

Calculated column on Horizontal Pivot

Anonymous — Sat, 15 May 2010 23:05:39 GMT

(Copying Oleg )
I was going to suggest using total, but didn't think of using rowno(). My typical "condition of choice" is dimensionality. I think it would be dimensionality2 in this case.
Now Rob has a lot of options...

Calculated column on Horizontal Pivot

rwunderlich — Mon, 17 May 2010 23:41:05 GMT

Thanks Andy. That's a great solution.

-Rob