Expression Question Using If/Where Logic

[JustinRamsey]

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. 

[rwunderlich · Accepted solution]

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

-Rob

[JustinRamsey]

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]

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]

Thanks so much for the clarification!