Sum of Values based on fields

[Anonymous]

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,

[jzimolong]

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.

[#3]

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:

[Anonymous]

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,

[Anonymous]

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.

[jzimolong]

LOAD

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

[koushik_btech20]

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

Thank & Regards,

Koushik Banerjee

[tresesco]

Like in attached qvw?

[#9]

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

[jzimolong]

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