How to sum specific records based on a text value

[c_rusu85]

Hello,

I am new here, I use QlikView Personal Edition. Please help me with an expression formula to sum some values based on a certain criteria. Here is my situation:

I have this database table in QlikView Personal Edition:

Shop	Order	Category	Value
S1	100	A	10
S1	100	B	10
S1	100	C	10
S2	100	A	10
S2	100	E	10
S1	101	A	10
S1	101	B	10
S2	101	E	10
S1	103	B	10
S1	103	C	10

Based on the above database table I made a total table (straight table) where I need to sum for each shop all rows of all orders that contain the category "A", not only the rows that contain the "A" category.

So for example, based on the above table:

- for Shop "S1" I need to sum the value of all rows of S1 orders 100 (3 rows) + 101 (2 rows)

- for Shop "S2" I need to sum the value of all rows of S2 order 100 (2 rows)

The total table should look like:

Shop list	Total A values
S1	50
S2	20

My question is what formula to use in the expression field "Total A values"?

[qv_testing]

Try this,

IF(Shop='S1', Sum({<Shop={'S1','S2'}, Order={100,101}>}Value),

IF(Shop='S2', Sum({<Shop={'S2'}, Order={100}>}Value)))

or

Sum({<Shop={'S1','S2'}, Order={100,101}>}Value)

and we can use P function also.

[c_rusu85]

The data in my example (shop S1, S2, order 100,101, etc) is just for example.

I have a database with over 100,000 orders and I need to make a formula based on the fields name not on the specific order value.

In the total table I put the field "Shop" as Dimension and field "Total A values" as expression.

For example if I want to make a total table only for the rows with the category "A" the formula I use is:

Sum ({<Category = {A'}>} Value)

But I need to make the total table for all the orders that include the category "A", not only the rows in the actual category "A". Sorry if I did not explain it clear.

[effinty2112 · Accepted solution]

Hi Cornel,

The magic of the P() operator comes to the rescue:

Shop	Sum({$<Order = P({<Category = {'A'}>})>}Value)
	80
S1	50
S2	30

We have a difference between the formula and your hand calculation. QV is returning 30 for S2 because:

Shop	Order	sum(Value)
		30
S2	100	20
S2	101	10

Cheers

Andrew

[arvind_patil]

Hi Cornel,

I think Andrew is right.

Thanks,

Arvind Patil

[Kushal_Chawda]

try this expression

=sum({<Order=p({<Category={'A'}>}Order)>}Value)

[effinty2112]

Arvind,

I'm like a stopped clock. I'm right twice a day.

Cheers

Andrew

[c_rusu85]

Thanks Andrew and Kushal, the formula worked perfectly!

[Kushal_Chawda]

if want exact output, refer belo

Data:

LOAD Shop,

     Order,

     Category,

     Value

FROM

[https://community.qlik.com/thread/268590]

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Data)

LOAD Shop,

     Order,

     1 as Flag

Resident Data

where Category='A';

Create chart

Dimension:

Shop

Expression:

=sum({<Flag={1}>}Value)