Summation of Negative value only

[gayatri7]

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.

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

[IamAlbinAntony]

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;

[gayatri7]

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

[agigliotti]

Maybe this:

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

I hope it can helps.

[consultant_bi]

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

[raji6763]

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

[sunny_talwar]

May be this

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