Where statements in a pivot table.

[#1]

Ok, tricky question.

I have a pivot table that we want to show Inventory Types (Shortage, On Hand, Book, Adjusted Books, Sales, etc.) Well, on the table, each one of Inventory types has an Inventory Dollar Amount. How can I add a column on my pivot table that will bring in JUST the dollar amounts where the Inventory Type Code = S for sales, B for books, etc.

Am I over thinking this process? My original thought was to have something like this as the expression for Inventory Sales.

=sum(Current_Year.INV_DOLLAR_AMT) where INV_TYP_CDE = 'S'

But QV tells me there is garbage after my statement.

Any help would be GREATLY appreciated.

Thanks,

Scott

[johnw · Accepted solution]

It's just a syntax issue. Set analysis is probably the most efficient way to handle this. Here's the set analysis syntax.

sum({<INV_TYP_CDE={'S'}>} Current_Year.INV_DOLLAR_AMT)

[#3]

In QV you shoud use if statements:

Try this:

sum(if(INV_TYP_CDE = 'S', Current_Year.INV_DOLLAR_AMT))

Regards,

[#4]

Yeah, that did the trick. Thank you very much! Silly syntax problems...haha.

[#5]

Set analysis is faster Ricardo. Check this link out:

http://community.qlik.com/forums/p/17188/71371.aspx#71371

[#6]

Thanks Matt you're right.

Best Regards,