topic Re: comparing different expression against different dimension... in QlikView

comparing different expression against different dimension...

Fri, 21 Jun 2013 13:00:22 GMT

Hi All,

It's Friday and it seems that my brain doesn't want to work anymore...

i have the table below

Dimension		Expression
Year	Term	A	B	C
2010	T1	64	64	58
2010	T2	66	54	45
2010	T3	51	47	50
2010	T4	60	61	38
2011	T1	61	66	50
2011	T2	59	69	47
2011	T3	49	58	48

I need to compare B in T2 2010 against A in T1 2010, C in T2 2010 against B in T1 2010 and so on...

Any idea on how I should tacle that?

I have set up a table where it give me the previous term compare to term n, but that doesn't seems to be the right route to take.

I am looking forward to your assistance.

Maxime

Re: comparing different expression against different dimension...

alexandros17 — Fri, 21 Jun 2013 13:10:52 GMT

column B for example:

sum(B) - above(Before(A))

Re: comparing different expression against different dimension...

Gysbert_Wassenaar — Fri, 21 Jun 2013 13:12:42 GMT

If A, B and C are expressions you can create new expressions that calculate the difference:

=B - above(A)

=C - above(B)

etc..

edit: since you're using two dimensions you need the total keyword in the above function to cross the dimension borders: B-above(total A)

Re: comparing different expression against different dimension...

Fri, 21 Jun 2013 13:29:52 GMT

Thanks Gysbert, That has worked perfectly!

Year	Term	A	B	C	A>B	B>C
2010	T1	64	58	29	-	-
2010	T2	66	45	55	70%	95%
2010	T3	51	50	40	76%	89%
2010	T4	60	38	51	75%	102%
2011	T1	61	50	37	-	-
2011	T2	59	47	45	77%	90%
2011	T3	49	48	40	81%	85%
2011	T4	69	41	46	84%	96%

Now, my next issue is that I do not want to show Column ABC but only A>B and B>C, as this is what I will need to represent in a chart. When converting it to chart and if I mark it as invisible it doesn't do the trick.

Would you have anything in you rhat that can do the trick?

Thanks again for your quick response,

Maxime

Edit, I have managed to hide the column without affecting my result but when I want to interpret it in a chart it really doesn't look great... i think I'll have to play more with it to figure it out.

Re: comparing different expression against different dimension...

Fri, 21 Jun 2013 13:53:06 GMT

I have to come back on that point as yes it is working but only up to 75%, I am unsure why the calculation for each T1 doesn't work. I need T1 to compare to T4 but it seems that using this particular expression it doesn't work.

Thanks for your help,

Maxime

Re: comparing different expression against different dimension...

Fri, 21 Jun 2013 14:06:18 GMT

Thanks Everything is working perfectly now thanks for your quick help!