Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
c_rusu85
Contributor II
Contributor II

How to sum specific records based on a text value

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:

ShopOrderCategoryValue
S1100A10
S1100B10
S1100C10
S2100A10
S2100E10
S1101A10
S1101B10
S2101E10
S1103B10
S1103C10

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 listTotal A values
S150
S220

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

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Cornel,

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

Shop Sum({$<Order = P({<Category = {'A'}>})>}Value)
80
S150
S230

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

Shop Order sum(Value)
30
S210020
S210110

Cheers

Andrew

View solution in original post

8 Replies
qv_testing
Specialist II
Specialist II

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
Contributor II
Contributor II
Author

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
Master
Master

Hi Cornel,

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

Shop Sum({$<Order = P({<Category = {'A'}>})>}Value)
80
S150
S230

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

Shop Order sum(Value)
30
S210020
S210110

Cheers

Andrew

View solution in original post

arvind_patil
Partner
Partner

Hi Cornel,

I think Andrew is right.

Thanks,

Arvind Patil

Kush
MVP
MVP

try this expression

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

effinty2112
Master
Master

Arvind,

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

Cheers

Andrew

c_rusu85
Contributor II
Contributor II
Author

Thanks Andrew and Kushal, the formula worked perfectly!

Kush
MVP
MVP

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)