topic Re: Accumalation based on partial field names in QlikView

Accumalation based on partial field names

tahirkhalil — Wed, 29 Jul 2015 06:08:37 GMT

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

Re: Accumalation based on partial field names

Wed, 29 Jul 2015 17:52:44 GMT

Sum(total <[Product Line]> Performance)

Try this

Thanks

Sai

Re: Accumalation based on partial field names

tahirkhalil — Thu, 30 Jul 2015 06:40:48 GMT

HI,

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

Regards,

Tahir

Re: Accumalation based on partial field names

tahirkhalil — Fri, 21 Aug 2015 12:10:31 GMT

Any clue guys?

Re: Accumalation based on partial field names

alex_millan — Fri, 21 Aug 2015 12:32:05 GMT

Hi Tahir,

check the attached file.

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

HTH

Regards

Re: Accumalation based on partial field names

tahirkhalil — Mon, 24 Aug 2015 06:42:08 GMT

Hi Alex,

You forget to attach file.

Re: Accumalation based on partial field names

jonathandienst — Mon, 24 Aug 2015 06:46:44 GMT

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

Re: Accumalation based on partial field names

alex_millan — Mon, 24 Aug 2015 06:59:45 GMT

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

Cheers

Re: Accumalation based on partial field names

tahirkhalil — Mon, 24 Aug 2015 09:25:49 GMT

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.

Re: Accumalation based on partial field names

tahirkhalil — Wed, 16 Sep 2015 09:59:23 GMT

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

Re: Accumalation based on partial field names

alex_millan — Wed, 16 Sep 2015 10:44:38 GMT

Are you sure that would do the trick? In some cases, the amount of data will not diminish dramatically, what's more, the difficulties to exploit this data will boost.

Perhaps, if the number of rows is an issue, you may go for a different approach trying to accumulate the data to the desired point in the script.

Hope that helps.

Cheers

Re: Accumalation based on partial field names

tahirkhalil — Wed, 16 Sep 2015 13:19:16 GMT

Can you please have a look - I have created a sample data.

I have created another table containing definitions - but the point it is selection based view - can we dig out more with this approach?

Re: Accumalation based on partial field names

PradeepReddy — Wed, 16 Sep 2015 13:51:35 GMT

Try some thing like this.. see the attachmen !t...

If it did not caters your requirement, post the output what u want..!!

Thanks,

Pradeep

Re: Accumalation based on partial field names

tahirkhalil — Thu, 17 Sep 2015 05:11:20 GMT

Thanks Pradeep !

But again, I dont want crosstab functionality due to higher number of rows.