Sum With Particular Products & Product number

[#1]

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

[Kushal_Chawda]

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)

[jonathandienst]

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.

[#4]

HI Kush

I tried But getting 0 for the field

[#5]

HI Jonathan

I Want only few product Number starting with 154

not the entire 154 product code

[jonathandienst · Accepted solution]

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)

[jonathandienst]

Or in script:

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

[#8]

HI Jonathan & Kush

Thanks a lot