Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinRamsey
Creator
Creator

Expression Question Using If/Where Logic

Good Morning,

I am needing to add a column in a table to display a sum of Quantity where StorageType=105. We have multiple storage types indicating where parts are stored (bulk, shelf, etc). I have tried using the expression if(StorageType='105',Quantity), it appears that is given me the total quantity if there is  a StorageType 105 established for that part. I am trying to build a table that has the part and individual columns for each storage type so we can monitor when parts are running low in standard storage to pull from other sources. 

Any idea what formula would do what I am asking? I have included a table view of the data, as you can see we have multiple lines for parts with different storage types. Would like to consolidate into one line per part with individual columns for the storage type quantities. 

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Sum({<StorageType={105}>} Quantity)

-Rob

View solution in original post

4 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Sum({<StorageType={105}>} Quantity)

-Rob

JustinRamsey
Creator
Creator
Author

That seems to be working. Can you explain exactly what the algorithm is for that function? I am mainly a mathematics guy so my brain is just reading that as a bunch of grouping symbols rather than some action taking function. 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The base aggregation function 

Sum(Quantity)

returns the sum of values in the field Quantity. Typed just as "Sum(Quantity)" it would aggregate the data using the current selections (filters) as made by the user. 

The bit between the {} is a Set Expression.  It modifies the current selections. A set expression is analogous to user selections. 

{<StorageType={105}>}

means "select 105 in  the StorageType field" before doing the Sum.  

You can read more about Set Analysis here https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/ChartFunctio...

Set Analysis is an alternative to "if" and it's more efficient and powerful.  BTW, your original expression probably would have worked as "Sum(if(StorageType=105,Quantity))".  You were just missing the Sum().

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

JustinRamsey
Creator
Creator
Author

Thanks so much for the clarification!