# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for
Did you mean:
Creator II

## Summation of Negative value only

Hi All,

I have below sample data where I just want to sum the negative values. For Eg. Year 2018 it should be -30, 2019 as -5 and 2020 as -45.

Once I have that,  I wanted to get the minimum value among those. Here it will be -45.

 Domain 2018 2019 2020 A 30 20 40 B 30 20 30 C 20 20 20 D -20 20 -30 E -10 -5 -15

Thanks

Gayatri

Labels (1)
• ### Qlik Sense

6 Replies
Contributor III

try this  (ignore cross table if you don't need the conversion),

Table1:

Domain,2018,2019,2020
A,30,20,40
B,30,20,30
C,20,20,20
D,-20,20,-30
E,-10,-5,-15
];

left join (Table1)

Table2:
Resident Table1
where Value<0
Group by Year;

left join (Table1)

Table3:
Resident Table1
where Value<0;

Creator II

Thanks for your reply. But data is not actually in Cross table. It's normal table.

I wanted to get that through Set Expression and not at script level. As these values I will be using in Bar Chart.

I was trying   something like below but not getting the summation of negative value. I tried AGGR function as well but not getting the expected result.

SUM({<Amount = {"<0"},Year=>}Amount )

To be clear I have my data as below (not in Crosstable)

 Year Domain Amount 2018 A 30 2018 B 30 2018 C 20 2018 D -20 2018 E -10 2019 A 20 2019 B 20 2019 C 20 2019 D 20 2019 E -5 2020 A 40 2020 B 30 2020 C 20 2020 D -30 2020 E -15

Partner

Maybe this:

=SUM( {< Amount = {"<0"} >} TOTAL <Year> Amount )

I hope it can helps.

Creator

Hello @gayatri7

let's used as a pivot table with two dimensions Period and Domain

for the measure :

if( field_number < 0,

sum(field_number))

or using a kpi  :

min(aggr(sum(field_number),Domain&Period))

Creator II

hi,

if you want to calculate sum by dimension, then you need to group the column

use the below expression

Sum({<Amount1={"<0"}>}Amount1)

and convert it into a pivot table.

regards,

raji

MVP

May be this

``Min(Aggr(Sum({<Amount = {"<0"}, Year>} Amount), Year))``
Tags