Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Calculated column on Horizontal Pivot

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

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

4 Replies
Not applicable

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable

(Copying Oleg Stick out tongue)
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...

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author

Thanks Andy. That's a great solution.

-Rob