Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ricardosilva
Contributor II
Contributor II

Count negative values per group

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.

ricardosilva_0-1623152806672.png

Please any one can help me.

Thanks in Advance

1 Solution

Accepted Solutions
MayilVahanan

Hi Ricard,

Try like below

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

5 Replies
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.

Thanks & Regards,
Mayil Vahanan R
ricardosilva
Contributor II
Contributor II
Author

Hi @MayilVahanan ,

with your formula, this is the result:

ricardosilva_0-1623666489108.png

 

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

Hi Ricard,

Try like below

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

MayilVahanan

O/P:

MayilVahanan_0-1623667692982.png

 

Thanks & Regards,
Mayil Vahanan R
ricardosilva
Contributor II
Contributor II
Author

🙏

Thanks Mayil