topic Where statements in a pivot table. in QlikView

Where statements in a pivot table.

Tue, 11 May 2010 21:54:38 GMT

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

Where statements in a pivot table.

johnw — Tue, 11 May 2010 22:32:50 GMT

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)

Where statements in a pivot table.

Tue, 11 May 2010 22:33:27 GMT

In QV you shoud use if statements:

Try this:

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

Regards,

Where statements in a pivot table.

Tue, 11 May 2010 22:43:21 GMT

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

Re:Where statements in a pivot table.

Wed, 12 May 2010 05:43:55 GMT

Set analysis is faster Ricardo. Check this link out:

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

Re:Where statements in a pivot table.

Wed, 12 May 2010 05:51:35 GMT

Thanks Matt you're right.

Best Regards,