Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hi @Mgrande
the formula seems to be correct. Could you, please, share a copy of the QVW / QVF ?
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