topic Conditional Expression in QlikView

Conditional Expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone!

I have this table:

Order no	S_Q	A_Q
A	100	125
B	200	199
C	300	280
D	400	0
E	0	500
F	600	400
G	550	550

I would like to show the difference between S_Q and A_Q that greater than 5% or less than -5%, the answer should be like these:

Order no	S_Q	A_Q	Diff	%diff
A	100	125	25	20%
C	300	280	-20	-7%
E	0	500	500	100%
F	600	400	-200	-50%

what is the expression?

Re: Conditional Expression

surendraj — Fri, 08 Dec 2017 14:39:55 GMT

May be!!

S_Q - A_Q/A-Q

Re: Conditional Expression

sunny_talwar — Fri, 08 Dec 2017 14:42:19 GMT

Should it be this

(A_Q - S_Q)/A_Q

Re: Conditional Expression

MK9885 — Fri, 08 Dec 2017 14:42:49 GMT

S_Q-A_Q as Diff in expression

Diff/A-Q as %diff in expression

Re: Conditional Expression

sunny_talwar — Fri, 08 Dec 2017 14:43:58 GMT

And may be this to should only difference above 5% and below -5%

If(fabs((A_Q - S_Q)/A_Q) > 0.05, (A_Q - S_Q)/A_Q)

Re: Conditional Expression

Anonymous — Fri, 08 Dec 2017 14:45:52 GMT

Hi Sunny Talwar

no it does not work!

It shows the all data but I want to show only the differences which are obey the condition.

Re: Conditional Expression

Anonymous — Fri, 08 Dec 2017 14:51:51 GMT

Hi again!

It works; the only task to do is that it shows all data and in this situation I would not like to show the B,D and G.

who we can do it?

Re: Conditional Expression

sunny_talwar — Fri, 08 Dec 2017 14:57:13 GMT

Isn't that we are seeing?

Re: Conditional Expression

Anonymous — Fri, 08 Dec 2017 15:13:08 GMT

Dear Sunny Talwar

I appreciate you.

Re: Conditional Expression

Anonymous — Fri, 08 Dec 2017 16:24:35 GMT

Hi Sunny Talwar

Can you look at this?

Order no	(A_Q - S_Q)	If(fabs((A_Q - S_Q)/A_Q) > 0.05, (A_Q - S_Q)/A_Q)
	-	-
A	25	20.00%
B	-1	-
C	-20	-7.14%
D	-400	-
E	500	100.00%
F	-200	-50.00%

How to remove columns B AND D?

Re: Conditional Expression

sunny_talwar — Fri, 08 Dec 2017 16:27:12 GMT

Try this

Dimension

Order no

Expressions

If(fabs((A_Q - S_Q)/A_Q) > 0.05, (A_Q - S_Q))

If(fabs((A_Q - S_Q)/A_Q) > 0.05, (A_Q - S_Q)/A_Q)

Basically, you need to restrict your other expressions to not show value when the percentage is less than 5% or greater than -5%...

Re: Conditional Expression

Anonymous — Fri, 08 Dec 2017 16:28:06 GMT

Is it right to add calculation dimension like this? (A_Q - S_Q)

why in siome cases I get this error:

// Error in calculated dimension

Re: Conditional Expression

sunny_talwar — Fri, 08 Dec 2017 16:33:39 GMT

You can do that too...

Dimension

=Aggr(If(fabs((A_Q - S_Q)/A_Q) > 0.05, [Order no]), [Order no])

Expression

(A_Q - S_Q)

(A_Q - S_Q)/A_Q