Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Several if-functions in formula (without sum/count etc.)

I want to add several if-functions in my formula and I get stuck if I want to complete the formula

E.g.:

Country, Customer, CustomerOrders

Netherlands, A, A5

Netherlands, B, B7

Netherlands, C, C8

Belgium, N, N8

Belgium, O, O5

Belgium, P, P6

Germany, A, A7

Germany, R, R7

Germany, S, S9

The goal is to show all CustomerOrders for the Netherlands for Customers A + B, and for Belgium for Customers O + P, and for Germany for the Customers R + S

I have a begin:
if(Country = Netherlands and (Customer = A or Customer = B), CustomerOrders))...but then I get stuck.

Can somebody help me with this formula?

1 Solution

Accepted Solutions
rwunderlich
Not applicable

Several if-functions in formula (without sum/count etc.)

Create the CustomerCoutry field as Dennis suggest, then use the set expression:

sum( {$<CustomerCountry= {NetherlandsA, NetherlandsB, BeligiumO}>} CustomerOrders)


or if you don't really have a field to sum, count instead

count( {$<CustomerCountry= {NetherlandsA, NetherlandsB, BeligiumO}>} OrderId)

I didn't list all the countries but you get the idea.

-Rob


4 Replies
rwunderlich
Not applicable

Several if-functions in formula (without sum/count etc.)

How about

if(match(Country & Customer,'NetherlandsA','NetherlandsB','BelgiumO','BelgiumP','GermanyR','GermanyS') >0

-Rob

dennisnet
Not applicable

Several if-functions in formula (without sum/count etc.)

You could also adjust your script and create a unique field which you use only in your expression.

Load:

(Country &'-'& Customer) as CountryCustumerKey

From ...

This will create:

field: CountryCustumerKey

               Netherlands-A

               Netherlands-B

               Netherlands-C

               Belgium-N

               Belgium- O

               Belgium-P

               Germany-A

               Germany-R

               Germany-S

This way you can use the CountryCustumerKey-field in your IF-statement.


Not applicable

Several if-functions in formula (without sum/count etc.)

It is more difficult, then I described in the example, the data set is incorrect, my apologies for this.

I want a sum of all customer orders which meets the conditions. ("The goal is to show all CustomerOrders for the Netherlands for Customers A + B, and for Belgium for Customers O + P, and for Germany for the Customers R + S")

So if this is the dataset:

Netherlands, A, 5

Netherlands, B, 7

Netherlands, C, 8

Belgium, N, 8

Belgium, O, 5

Belgium, P, 6

Germany, A, 7

Germany, R, 7

Germany, S, 9

I want to see:
Customer Orders = 39

sum of (5+7+5+6+7+9)

Thnx in advance,

ZR

rwunderlich
Not applicable

Several if-functions in formula (without sum/count etc.)

Create the CustomerCoutry field as Dennis suggest, then use the set expression:

sum( {$<CustomerCountry= {NetherlandsA, NetherlandsB, BeligiumO}>} CustomerOrders)


or if you don't really have a field to sum, count instead

count( {$<CustomerCountry= {NetherlandsA, NetherlandsB, BeligiumO}>} OrderId)

I didn't list all the countries but you get the idea.

-Rob