Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"?
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
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.
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.
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
Hi Cornel,
I think Andrew is right.
Thanks,
Arvind Patil
try this expression
=sum({<Order=p({<Category={'A'}>}Order)>}Value)
Arvind,
I'm like a stopped clock. I'm right twice a day.
Cheers
Andrew
Thanks Andrew and Kushal, the formula worked perfectly!
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)