Count negative values per group

[ricardosilva]

Hi

I need to calculate the last column, that is the count of store by prod stat  with negative var €.

I already tried with aggr, but no success.

Please any one can help me.

Thanks in Advance

[MayilVahanan]

HI @ricardosilva 

Try like below

Sum(Total Aggr(If(Sum(Y)-sum([Y-1]) < 0, 1), ProdStat, Store))

Sum(Y)-sum([Y-1]) <-- replace with ur variance formula.

[ricardosilva]

Hi @MayilVahanan ,

with your formula, this is the result:

This is the script i used (if it's useful)

Load * inline [
PROD STAT, STORE, YEAR, AMT
1,A,2020,5
1,A,2021,6
1,B,2020,4
1,B,2021,8
1,C,2020,6
1,C,2021,5
2,A,2020,6
2,A,2021,5
2,B,2020,10
2,B,2021,8
2,C,2020,6
2,C,2021,5
3,A,2020,6
3,A,2021,5
3,B,2020,8
3,B,2021,8
3,C,2020,6
3,C,2021,5
];

Thanks

[MayilVahanan · Accepted solution]

Hi Ricard,

Try like below

Sum(Total<[PROD STAT]> Aggr(If(Sum({<YEAR={2021}>}AMT)-sum({<YEAR={2020}>}AMT) < 0, 1), [PROD STAT], STORE))

[MayilVahanan]

O/P:

[ricardosilva]

🙏

Thanks Mayil