Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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".

11 Replies
martinpohl
Partner - Master
Partner - Master

use set analysis

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

regards

luismadriz
Specialist
Specialist

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
Not applicable
Author

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
Specialist
Specialist

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

Anonymous
Not applicable
Author

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
Specialist
Specialist

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
Specialist
Specialist

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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