Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
Creator II
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.

Could any one please help me with this.

 

Domain201820192020
A302040
B302030
C202020
D-2020-30
E-10-5-15

 

Thanks

Gayatri

Labels (1)
6 Replies
Contributor III
Contributor III

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

Table1:
Crosstable (Year,Value) Load *;
Load * inline [

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:
Load Year,sum(Value) as NegValue
Resident Table1
where Value<0
Group by Year;

left join (Table1)

Table3:
Load min(Value) as MinNegValue
Resident Table1
where Value<0;

Creator II
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)

YearDomainAmount
2018A30
2018B30
2018C20
2018D-20
2018E-10
2019A20
2019B20
2019C20
2019D20
2019E-5
2020A40
2020B30
2020C20
2020D-30
2020E-15

 

 

Partner
Partner

Maybe this:

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

I hope it can helps.

Creator
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
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.

raji6763_1-1589282859750.png

regards,

raji

May be this

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