Still new to Qlik Sense. Having trouble with "if and Or"

[Anonymous]

Creating a calculated field to a table. I want to only sum only those sales transaction which have been characterized in any of 7 different ways that transaction should be added to the total.

If([Merchant-defined Order Status] = 'Completed' ,[Merchant-defined Order Status]),[product unit price}). This works but when I try to add other Merchant defined  statuses, such as shipped' using "or" it gives me an error of "unexpected token".

[martinpohl]

use set analysis

sum({<[Merchant-defined Order Status]={'Completed','Shipped'}>} Value)

regards

[luismadriz]

Hi Howard,

Martin's is the way to go as Set Analysis provide better performance and more flexibility, but sometime you need to use Ifs to get your head around some of that. This is an option depending of what you need to do:

SUM(If([Merchant-defined Order Status] = 'Completed' or [Merchant-defined Order Status] = 'Completed',ValueToSumOrAggregate))

Not sure what you want to do as you have [product unit price] which is not something that normally gets aggregated on its own, but it all depends on your design,

Cheers,

Luis

[Anonymous]

Thank you for the help but still getting a error message "unrecognized symbol" {

sum({<[Merchant-defined Order Status] = 'Completed','Shipped','Awaiting Payment','Awaiting Shipment','Partially Shipped','Partially refunded'>}[Product Unit Price])
Any thoughts

[luismadriz]

sum({<[Merchant-defined Order Status] ={'Completed','Shipped','Awaiting Payment','Awaiting Shipment','Partially Shipped','Partially refunded'}>}[Product Unit Price])

[Anonymous]

Luis,

I copied and pasted the line of code into the edit box and it still came up

with the left curly bracket "{" as an "unrecognized symbol" still

frustrating but there must be an explanation.

Howard Levin

On Sun, Jan 7, 2018 at 5:54 PM, Luis Madriz <qcwebmaster@qlikview.com>

[luismadriz]

Hi Howard,

As I know that expression is fine I'm thinking that you're in the load script where Set Analysts does not work

Do you want to try that expression in the front end?

If you still need to do it in the load script, can you please share your script and your requirement?

I hope this helps,

Cheers,

Luis

[luismadriz]

If, on the load script, the expression could be something like this:

SUM( IF(Match([Merchant-defined Order Status],'Completed','Shipped','Awaiting Payment','Awaiting Shipment','Partially Shipped','Partially refunded'),[Product Unit Price]) ) as SumOrdersWithStatus

When using this expression you may need to be mindful of the Group By at the end of the load for that table

I hope this helps,

Luis

[rwunderlich]

Set Analysis is the way to go in most cases.  But if you must use an If(), testing multiple values is easier using match vs OR.

If(Match([Merchant-defined Order Status] ,'Completed', 'Shipped', 'Cancelled')....

See also MixMatch() and WildMatch().

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

Luis,

I am in the section of Data manager editing a table by adding a calculated field not a load section. It may be that loading is where I need to be.

Howard