Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Mgrande
Contributor
Contributor

Sum, subtract in count return 0 count Qlik Sense

I am trying to count customers are greater less than 0 in saless subtracting total sales between two years in a KPI, but do not count all rows :

=count({<[Customer_id] = {"=(Sum({<Year={$(=Max(Year))}>}[sales])-Sum({<Year={$(=Max(Year)-1)}>} [sales]))<0"}>} distinct [Customer_id] )

The result of count is 14, and it must be 16. 

In the table it count two rows like 0

[customer_id] Sales (2021) Sales (2020) Sales subtract Result Count(distinct [customer_id])
0900007 21810,7 26260,59 Down -4449,89 1
0900014 6794,36 8295,13 Down -1500,77 1
2400004 47445,43 49895,94 Down -2450,51 1
2400009 6326,87 6505,95 Down -179,08 1
2400014 0 9724,89 Down -9724,89 0
2400015 0 20893,03 Down -20893,03 1
2400016 2858,46 3571,75 Down -713,29 1
2400019 109278,07 120445,45 Down -11167,38 1
2600002 21903,16 28260,08 Down -6356,92 1
2600003 9616,61 10919,84 Down -1303,23 1
3400010 0 530,76 Down -530,76 0
4000006 5029,8 6407,28 Down -1377,48 1
4000008 2857,98 3748,14 Down -890,16 1
4000009 116,44 866,36 Down -749,92 1
4000012 1609,21 2504,88 Down -895,67 1
4000015 -274,68 1194,93 Down -1469,61 1
Labels (2)
2 Replies
Andrea_Bertazzo
Support
Support

Hi @Mgrande 

the formula seems to be correct. Could you, please, share a copy of the QVW / QVF ?

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up ! 🙂
Mgrande
Contributor
Contributor
Author

Hi @Andrea_Bertazzo ,

I'll try but it will take time, it's too big, I have to drop tables which doesn't affect this issue. Thanks