topic Re: How to sum specific records based on a text value in QlikView

How to sum specific records based on a text value

c_rusu85 — Wed, 25 Nov 2020 16:16:04 GMT

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"?

Re: How to sum specific records based on a text value

Nagaraju_KCS — Mon, 24 Jul 2017 09:12:33 GMT

Try this,

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

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

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

and we can use P function also.

Re: How to sum specific records based on a text value

c_rusu85 — Mon, 24 Jul 2017 09:25:24 GMT

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.

Re: How to sum specific records based on a text value

effinty2112 — Mon, 24 Jul 2017 09:30:13 GMT

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

Re: How to sum specific records based on a text value

arvind_patil — Mon, 24 Jul 2017 09:33:47 GMT

Hi Cornel,

I think Andrew is right.

Thanks,

Arvind Patil

Re: How to sum specific records based on a text value

Kushal_Chawda — Mon, 24 Jul 2017 09:35:32 GMT

try this expression

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

Re: How to sum specific records based on a text value

effinty2112 — Mon, 24 Jul 2017 09:36:39 GMT

Arvind,

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

Cheers

Andrew

Re: How to sum specific records based on a text value

c_rusu85 — Mon, 24 Jul 2017 09:51:16 GMT

Thanks Andrew and Kushal, the formula worked perfectly!

Re: How to sum specific records based on a text value

Kushal_Chawda — Mon, 24 Jul 2017 09:51:48 GMT

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)

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

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

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