topic Re: Count negative values per group in QlikView

Count negative values per group

ricardosilva — Tue, 08 Jun 2021 11:53:59 GMT

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

Re: Count negative values per group

MayilVahanan — Wed, 09 Jun 2021 01:53:50 GMT

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.

Re: Count negative values per group

ricardosilva — Mon, 14 Jun 2021 10:42:32 GMT

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

Re: Count negative values per group

MayilVahanan — Mon, 14 Jun 2021 10:47:44 GMT

Hi Ricard,

Try like below

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

Re: Count negative values per group

MayilVahanan — Mon, 14 Jun 2021 10:48:22 GMT

O/P:

Re: Count negative values per group

ricardosilva — Mon, 14 Jun 2021 12:21:31 GMT

🙏

Thanks Mayil