topic Re: Expression Question Using If/Where Logic in QlikView

Expression Question Using If/Where Logic

JustinRamsey — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Expression Question Using If/Where Logic

rwunderlich — Fri, 08 Mar 2019 17:05:03 GMT

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

-Rob

Re: Expression Question Using If/Where Logic

JustinRamsey — Fri, 08 Mar 2019 18:37:40 GMT

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.

Re: Expression Question Using If/Where Logic

rwunderlich — Fri, 08 Mar 2019 20:17:23 GMT

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.

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

Re: Expression Question Using If/Where Logic

JustinRamsey — Fri, 08 Mar 2019 20:50:13 GMT

Thanks so much for the clarification!