Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dealing with dimensions and across dimension calculations

Hi Everyone,

I'm hoping someone has used an innovative solution to a common problem I face.

Say we have data for Sales by year.  Let's say we have 10 years worth of sales at an item level.

I build a pivot table that has Store and Year as the dimensions, so store on the left vertical and year on the top horizontal.

I can then show sum(Sales) for that data.  This is fine.  Now, if I want to do some sort of calculate between years, so for example, what is the sum of sales foor this year and the previous 3 years combined.  But I want this to be under this years dimension.  How best to do this? 

The problem is that when using a dimension, you inherently restrict the data in each cell by the category of that dimension.  e.g. 2010 would only have data for 2010 in that column so to do a calculation with 2009 data doesn't work.

One way I get around it is to have an unlinked table with year in it and then use if statements to do my calculations and use the non-linked year as my dimension:

if( year = year.not.linked, sum(Sales))

or

if( year = year.not.linked, sum(Sales)) - if(year = (year.not.linked - 1), sum(Sales))

This works of course, but as we all know, if statements are very inefficient in QV.  When my formulas get more and more complicated, the system grinds to a halt.

Does anyone have an innovative solution for me?

Thanks

Gareth

9 Replies
IAMDV
Luminary Alumni
Luminary Alumni

Gareth - Have you considered using Set Analysis & Dollar Sign Expansion. Please can you post the QV document with sample data? Also it will be useful if you can write comments on your sample QV document.

Cheers - DV

Not applicable
Author

Hi mate,

Thanks for your reply.  I use Set analysis all the time but in this scenario I can't figure out how to do it because by definition, the dimension restricts that data.  So if my categories are A,B,C, if I use this as a dimension I can only do calculations on A in the A row etc... so doing a set with C in doesn't work.  Can you explain what you mean by $ sign expansion?

I have an example doc for you but can't figure out how to post it on this new system!

Cheers,

G

Not applicable
Author

Ok I figured out I could edit the original post.

QVW attached.

Cheers,

g

IAMDV
Luminary Alumni
Luminary Alumni

Gareth - I had tried the below expression and it works fine. I am not sure if I had completely understood the requirement. Please let me know if this is what you need.

As mentioned above... Dollar Sign Expansion in Qlikview is very powerful feature. I had created the variable which calculates the Total Sales for 'C' in our example. I had created the variable in the Variable Overiew window (Alt + Ctrl + V : Keyboard Shortcut)... however, you can create the same in the load script if the value changes only when there is data load. In our case, I am assuming that we need the variable need to be dynamic calculation.

vC_Sales = (sum( {1<Category = {'C'}>} Sales))

This variable returns 468 in our example. And now we will use this variable in our expression to divide the Total Sales for 'C' across all the dimensions. Below is the expression (Dollar Sign Expansion).

sum(Sales)/ $(=($(vC_Sales)))

You are right! QV can't calculate the value for a specific dimension across the same dimension. It returns a null value for all the dimension except the specific dimension value and by using dollar sign expansion QV treats this as seperate unit, logically which is right. But techically it is the same value but we are just clever to trick QlikView

Let me know if this helps!

Cheers - DV

IAMDV
Luminary Alumni
Luminary Alumni

Gareth - I am not sure if you got chance to look at previous post... but I am very keen to know your thoughts.

Thanks in advance.

Cheers - DV

IAMDV
Luminary Alumni
Luminary Alumni

Gareth - Have you got chance to look at the solution?

Thanks - DV

johnw
Champion III
Champion III

You could generate (don't hard code it) a table like this:

AsOfYear, YearType, Year
2011,Current,2011
2011,Last 3, 2010
2011,Last 3, 2009
2011,Last 3, 2008
etc.

To compare the current year to the last three years side by side, just use AsOfYear in your chart instead of Year, and add YearType as another dimension.  If you want to actually see a percent change or something, though, you'd probably use set analysis to recognize the year type, and have multiple expressions:

Current = sum({<YearType={'Current'}>} Sales)
Last 3  = sum({<YearType={'Last 3'}>} Sales)
% Current vs. Avg of Last 3 = 3*Current/"Last 3" - 1

Not that these will be your exact expressions, but that's the idea.  For absolute maximum performance, use 1/null flags instead of or in addition to the YearType field, and use the flags in the set analysis.  Probably won't make much difference in most cases, though.

Not applicable
Author

Hi mate,

Yes, thanks very much.  In my situtation it will not work to use a variable.  Unfortunately it's more complicated than what I showed in the example QVW and is actually a very difficult question to articulate.

Thanks

Gareth

Not applicable
Author

Thanks John,

This is a useful idea!  I think this is exactly what I'm looking for.

Gareth