Accumalation based on partial field names

[tahirkhalil]

Hi all,

I am stuck up in one project - I need to sum up based on partial field names, I have following three basic KPIs

1. PRO-Proposition

2. PLN-Planogram

3. SOS-Share of Shelf

I have all three fields for different product lines like Haircare, Skincare, Beverages etc. I got total 10 product lines and 3 KPIs which makes total 30 fields. Now, I have to present the accumulated/Sum of all three KPI based on Product Line like:

Product Line	KPI	Performance
Haircare	PLN	12
	PRO	23
	SOS	10
Beverages	PLN	11
	PRO	10
	SOS	13
Skincare	PLN	10
	PRO	9
	SOS	11

How can I achieve that?

I have attached sample data for your reference.

I have attached again as it was found missing during post.

Regards,

Tahir

Message was edited by: Tahir Khalil - Sample Data File Attachment Missing

[#2]

Sum(total <[Product Line]> Performance)

Try this

Thanks

Sai

[tahirkhalil]

HI,

Can you please see the sample data in XLS - I was unable to attach it before.

Regards,

Tahir

[tahirkhalil]

Any clue guys?

[alex_millan]

Hi Tahir,

check the attached file.

You must load your table as crosstable, then split one field to separate ProductLine and KPI.

HTH

Regards

[tahirkhalil]

Hi Alex,

You forget to attach file.

TK

[jonathandienst]

I suggest that you post the expected results, as the numbers could be "accumulated" in several different ways.

[alex_millan]

I see the attached file in my previous post but... I'm sending it in again, just in case...

Cheers

[tahirkhalil]

I want to accumulate performance based on KPI. As you may have seen my XLS - I have total 30 fields - 3 KPIs each have 10 categories/product line. These 30 field names are [KPI]_[ProductLine] like PLN_HairCare contain the result. I want to make a bar chart stating HairCare on X-Axis while 3 separate bars stating all three KPI values.

[tahirkhalil]

Alex - what if I avoid the crosstab functionality. I want to keep reduced data set in terms of rows.