topic Re: Sum With Particular Products & Product number in QlikView

Sum With Particular Products & Product number

Mon, 08 Jun 2015 12:23:11 GMT

Hi Experts

I am Using a Pivot Table

in that I want to sum the sales for particular [Product code] & Product numbers,

How can I do it

sum(if(Wildmatch([Product Code], '155','156','157',158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','176'), Sales))

and i want to include certain product number also

Product Number (154321,154322,154323,154324)

Please Suggest

Re: Sum With Particular Products & Product number

Kushal_Chawda — Mon, 08 Jun 2015 12:26:21 GMT

Try like this

sum({<[Product Code]={'155','156','157',158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','176'},[Product Number]={'154321','154322','154323','154324'}>}Sales)

Re: Sum With Particular Products & Product number

jonathandienst — Mon, 08 Jun 2015 12:30:16 GMT

Do you mean that you are looking for codes that start with 155, 156 etc? Then add a wildcard to each start string like this:

'155*', '156*', '157*',....

I would probably create a derived variable flag to identify all those product codes

LOAD ....

[Product Code],

If(Wildmatch([Product Code], '155*', ....'176*') > 0, 1, 0) as CodeFlag,

....

Now you can use a set expression:

Sum({<CodeFlag = {1}>} Sales)

Which looks better and is usually much better performing.

Re: Sum With Particular Products & Product number

Mon, 08 Jun 2015 12:48:02 GMT

HI Kush

I tried But getting 0 for the field

Re: Sum With Particular Products & Product number

Mon, 08 Jun 2015 12:50:11 GMT

HI Jonathan

I Want only few product Number starting with 154

not the entire 154 product code

Re: Sum With Particular Products & Product number

jonathandienst — Mon, 08 Jun 2015 12:53:17 GMT

Then you need a union in the set expression like this:

sum({<[Product Code]={'155','156','157',158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','176'}>+<[Product Number]={'154321','154322','154323','154324'}>}Sales)

Re: Sum With Particular Products & Product number

jonathandienst — Mon, 08 Jun 2015 12:55:28 GMT

Or in script:

If(Match([Product Code], '155', ....'176') > 0 Or Match([Product Number],'154321','154322','154323','154324') > 0, 1, 0) as CodeFlag,

Re: Sum With Particular Products & Product number

Mon, 08 Jun 2015 12:57:37 GMT

HI Jonathan & Kush

Thanks a lot