Nexel Calculation Problem

Previous Member

I am trying to use COUNTIF in nexel but it is not giving me what I am expecting. The formula is as follows for each block in the Priority column: -

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;1;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;2;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;3;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;4;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;5;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;6;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;7;*] )

This is what it is giving me in Board.

Product	Min Qty	Safety Stock	Max Qty	Daily Sales Qty	FG Stock	Stock Days	Priority
1	7048	42285	211427	7472	84571	11.318	6
2	30504	85411	228780	32341	128117	3.961	1
3	13677	76594	205161	14501	114890	7.923	4
4	2668	30011	80030	2828	60022	21.224	6
5	4506	18296	337956	4777	28388	5.943	4
6	9683	27113	242084	10266	40670	3.962	3
7	31528	39410	204933	33427	78820	2.358	0

 

This is what the same calculation gives me in Excel.

 

A	B
11.318	6
3.961	2
7.923	5
21.224	7
5.943	4
3.962	3
2.358	1

 

=COUNTIF(A$2:A$8,"<="&$A2)

=COUNTIF(A$2:A$8,"<="&$A3)

=COUNTIF(A$2:A$8,"<="&$A4)

=COUNTIF(A$2:A$8,"<="&$A5)

=COUNTIF(A$2:A$8,"<="&$A6)

=COUNTIF(A$2:A$8,"<="&$A7)

=COUNTIF(A$2:A$8,"<="&$A8)

 

Thanks in advance

Unknown

Hi Andrew Smeets,

 

Might be due to rounding in dataviews cells - can you round the numbers to 3 decimal digits and check if it's still the case?
Michele

Previous Member

Hi Michele

All dataview cells are 0 (zero) except for the priority column which is 3.

Andrew

Previous Member

I mean the Stock Days column is set to 3. All others 0.

Unknown

Hi Andrew,

i tried now and it works fine for me when rounding the stock days to 3 digits with a column algorithm.

 

 

I tried by inputing the exact numbers and it also worked fine, then i changed them slightly (different number of decimal digits after the 3rd even though i was still displaying 3), added a round algorithm and calculated the countif on top of the rounded block.

What i believe is happening is that the calculation / data for the stock days has actually more than the 3 digits you display and when Nexel runs it actually calculates on the original full value with all decimals, not on 3 decimals. Forcing it to be exactly 3 decimals with a round formula in an algorithm should make it work.

Try and let me know  

Michele

Previous Member

Hi Michele

Thanks very much that works. It does not make sense though.

Andrew

Unknown

When you set a decimal digits display in a BOARD block, it is ignored in Nexel - it's just for visualisation of the data, it doesn't apply a real rounding (you have to specify the rounding as a column algorithm if you want that). When Nexel runs it takes the value at maximum precision.

I agree with you that it should work anyway, but what most likely happening the Nexel library is not able to run properly the IFCOUNT comparison at the precision coming from the BOARD layout in this case - so one of the 2 elements in the IFCOUNT is at a different level of precision. by forcing a rounding in the layout itself we solve this.

Previous Member

Thanks again. First time using Nexel. I will remember that for next time.