Pivot table and Set analysis issue

[#1]

Hello Guru's ,

I have a situation where I would like pivot table to behave a bit different to it's normal behavior.

We have a BOM table

Example:

BOM     Ingredient A     Ingredient B     Ingredient C     Ingredient D

Mat1     20%                0%                 40%               40%

Mat2     25%               35%                40%               0%

Mat3      0%               15%                 50%               35%

Mat4     55%                0%                 0%                 45%

Mat5     15%               55%                30%               0%

My Pivot table looks like above. When I filter my report based on Ingredient B, it shows me output

BOM     Ingredient B

Mat2     35%

Mat3     15%

Mat5     55%

However, my requirement is to see output like this:

BOM     Ingredient A     Ingredient B     Ingredient C     Ingredient D

Mat2     25%               35%                40%               0%

Mat3      0%               15%                 50%               35%

Mat5     15%               55%                30%               0%

Any help would be greatly appreciated. Example attached.

Thanks

Pankaj

[#2 · Accepted solution]

Hi,

Please find the attached qvw file.

Regards

Nandkishor

[#3]

Awesome. It works. If you don't mind can you please refer me to some reading material where can I find more about the logic you used?

Thanks a lot for your response.

Cheers

Pankaj