topic Re: subtract data in expression in QlikView

subtract data in expression

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

Dear all

Kindly advise how to create a 3rd column in expression to show the unit price difference between outlet A and outlet B.

Thanks

Tracy

Re: subtract data in expression

sunny_talwar — Thu, 22 Sep 2016 15:04:02 GMT

Like this?

Expression:

If(SecondaryDimensionality() = 0, Avg({Outlet<Outlet = {A}>} [Unit Price]) - Avg({Outlet<Outlet = {B}>} [Unit Price]), Avg({Outlet} [Unit Price]))

And make the following changes on the Presentation tab

Re: subtract data in expression

effinty2112 — Thu, 22 Sep 2016 15:05:12 GMT

Hi Tracy,

What about a straight table?

Item#	A	B	A - B
1	$4.50	$4.50	$0.00
2	$4.50	$4.50	$0.00
3	$7.00	$7.00	$0.00
4	$5.00	$5.00	$0.00
5	$7.00	$7.50	-$0.50
6	$7.00	$7.00	$0.00
7	$7.00	$7.00	$0.00
8	$8.50	$8.50	$0.00
9	$8.50	$8.50	$0.00
10	$15.00	$12.00	$3.00

Expression A has the formula: Sum({$<Outlet = {'A'}>}[Unit Price])

Expression B has the formula: Sum({$<Outlet = {'B'}>}[Unit Price])

Cheers

Andrew

Re: subtract data in expression

tracycrown — Fri, 23 Sep 2016 02:45:26 GMT

Dear Sunny

Please advise what is the meaning of SecondaryDimensionality() = 0. What happen if I have 10 outlets and I want to show the NEW AVERAGE in 3rd column instead of just A-B.

Thank you

Tracy

Re: subtract data in expression

sunny_talwar — Fri, 23 Sep 2016 07:10:24 GMT

Read about secondarydimensionality() function here: The second dimension... or how to use secondarydimensionality()

So instead of showing A-B you would want to show Average of 10 outlets? You can probably do this

If(SecondaryDimensionality() = 0, Avg({Outlet} [Unit Price]), Avg({Outlet} [Unit Price]))