Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

subtract data in expression

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

1 Solution

Accepted Solutions

Like this?

Capture.PNG

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

Capture.PNG

View solution in original post

4 Replies

Like this?

Capture.PNG

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

Capture.PNG

View solution in original post

Master
Master

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

Creator III
Creator III

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

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]))