Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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 |
Maybe this:
=SUM( {< Amount = {"<0"} >} TOTAL <Year> Amount )
I hope it can helps.
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))
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
May be this
Min(Aggr(Sum({<Amount = {"<0"}, Year>} Amount), Year))