topic Re: Sum of Values based on fields in QlikView

Sum of Values based on fields

Anonymous — Tue, 13 Jan 2015 02:27:08 GMT

Hi,

I have a list of values on the basis of product name, as mentioned below.

Question:

I need a sum of values in a table box when I select any one product in list box.

For example: I select ABC1 in a list box then the result should be "9" in table box and only show ABC1 not ABC1-00 and ABC1-01.

Sample Data,

Product Name	Value
ABC1	2
ABC1-00	3
ABC1-01	4
CDE1	1
CDE1-00	5
CDE1-01	3

Required Output,

Product Name	Value
ABC1	9

In a same way, when I select CDE1, then the result should be "9". All the time I need to show only one value and remaining values should be hide(Ex. ABC1-00, and ABC1-01).

You can consider, ABC1 as a child, ABC1-00 as a mother, and ABC1-01 as a father, same things for CDE.

Regards,

Re: Sum of Values based on fields

jzimolong — Tue, 13 Jan 2015 02:34:56 GMT

Add a column named ParentProduct in the load that trims off the "-XX", then you can simply display a chart that does sum based on the ParentProduct as dimension. When you create expression Sum(Value), use a little set analysis instead: =sum({1}Value)

This will show calculation based on ParentProduct, no matter what user selects.

Re: Sum of Values based on fields

Tue, 13 Jan 2015 03:07:10 GMT

Step 1:

Create a List Box with the following expression: =if (len(ProductName)=4, ProductName)

Step 2: Create a Variable without definition just name it vProductSearch

Step 3: Assign a Trigger to the ProductName, so that when Selected it assigns the following value to the variable vProductSearch

=only(ProductName) & '*'

Step 4: Create a Straight Table dimension Product Name and in the expression this value:

sum( { <ProductName= {'$(vProductSearch)'} >} Value )

Happy Qliking

Attached Document and Some Screen Shot:

Re: Sum of Values based on fields

Anonymous — Tue, 13 Jan 2015 04:49:01 GMT

Hi Joseph,

Is this possible to share me sample file.

I tried to trim in this way, but it is not working,

Trim('-00') as ParentProduct,

Trim(ProductName) as ParentProduct

Regards,

Re: Sum of Values based on fields

Anonymous — Tue, 13 Jan 2015 04:50:21 GMT

Hi Mario,

You are working fine, but in your table sample all the fileds are going to be show, however, i need only ABC1 and Value 9 nothing else. please share me updated sample file.

Re: Sum of Values based on fields

jzimolong — Tue, 13 Jan 2015 04:53:35 GMT

LOAD

ProductName,
SubField(ProductName,'-',1) as ParentProduct,
Value
FROM .........

Re: Sum of Values based on fields

koushik_btech20 — Tue, 13 Jan 2015 05:05:02 GMT

Follow the attached application. I think it will meet your requirement.

Thank & Regards,

Koushik Banerjee

Re: Sum of Values based on fields

tresB — Tue, 13 Jan 2015 05:06:28 GMT

Like in attached qvw?

Re: Sum of Values based on fields

Tue, 13 Jan 2015 05:08:40 GMT

sales:

Load * Inline [

ProductName, Value

ABC1, 2

ABC1-00, 3

ABC1-01 ,4

CDE1 ,1

CDE1-00 ,5

CDE1-01 ,3

];

groupTotals:

load ProductName, left(ProductName,4) as GroupName,

Value as GroupValue

resident sales;

and then make the list box on GroupName , the straight table is based on GroupName and expression sum(GroupValue) , thats it, if you want to place detailed table box, then you can place ProductName, Value to see how all these is associated by Qview automatically

Re: Sum of Values based on fields

jzimolong — Tue, 13 Jan 2015 05:13:36 GMT

For what it's worth......