topic Indirect set analysis help in QlikView

Indirect set analysis help

Fri, 26 Nov 2010 12:51:41 GMT

I am trying to plot year-1 sales by quarter on a graph using the following expression:

sum({$<[Year] = P({$}[Y&Q-1 Key]), [Y&Q Key]=, [Year & Quarter]=, [Quarter]=>}Sales)

So, when a year is selected from the calendar, I want to display the sum of the previous year's sales by quarter.

My date dimensions are as follows:

I've switched off the other data dimensions in my expression - I'm not sure if that is necessary - it didn't make any difference.

I know I could use Year=Year-1 in a set but I intend to use the solution in a chart with a continuous x axis i.e. 2007 Q1 - 2010 Q4 which would require the user selecting 2007, 8, 9 and 10 from the calendar so Year-1 won't work.

Please can someone tell me what I'm doing wrong?

Many thanks.

Indirect set analysis help

Mon, 29 Nov 2010 10:13:29 GMT

Does anybody have an idea on this?

Indirect set analysis help

marcel_olmo — Mon, 29 Nov 2010 10:33:20 GMT

I'm not pretty sure about what you really want, but maybe you're gonna need "inter registry" functions like above(), to get an accumulative number by quarters.

When I need to use inter registry functions, I always do something like this :

if(rowno()=1 ,myexpression() , myexpression() + above(column(1)) )

If this is the first row, I do my expression, but if not, I make a summation between the current row and the above one.

Let me know if this is what you want.

Cheers!!

Indirect set analysis help

Mon, 29 Nov 2010 11:31:41 GMT

Hi Marcel, nice idea but it isn't quite what I need.

Ultimately, I want to have a chart with a continuous x axis with Year & Quarter as my dimension and the expression to calculate growth vs. previous year. The chart below shows sales for 2008,9 and 10 by quarter.

I want to modify this to show growth vs. previous year by quarter e.g. 2010 Q1 vs. 2009 Q1. Because I have selected more than one year, Year=Year-1 will not work.

I thought by creating a Year & Quarter-1 Key I could do something along the lines of Year=Year & Quarter-1 Key to return the previous year's sales and then use this to calculate growth (see my expression in the first post).

I'm sure I've seen this type of solution used elsewhere. I think the logic is ok but I just can't get it to work. It just returns zero.

Hopefully this makes sense.

Any help will be greatly appreciated.