Forum

Group By - Unable t...
 
Notifications
Clear all

Group By - Unable to SUM Zeros

6 Posts
3 Users
0 Reactions
534 Views
(@patri0t82)
Posts: 43
Trusted Member
Topic starter
 

Hello there, I'm encountering an error that doesn't make any sense to me. I have three columns. I'm trying to Group by the first two and SUM the third. The third column is decimal numbers, however when I SUM them, all the zeros are returned as errors.

I've tried some things like changing the number column to different types prior to grouping, but nothing seems to be working. Is this correct behaviour? Should I just replace the errors with zeros?

 

Thanks for your help

 
Posted : 11/07/2020 8:34 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Adam,

It should simply return a group with zero total. Do the zeros show as errors before you applied the grouping? I'd try replacing zeros with null before grouping.

Mynda

 
Posted : 11/07/2020 7:08 pm
(@patri0t82)
Posts: 43
Trusted Member
Topic starter
 

Mynda, whatnks for your reply. I'm afraid that didn't work. I'm not sure how I can possibly send you an example book as it's entirely made up of workers' names, companies and workforce hours. 

I suppose the best I can do is describe better.

 

Column A contains Month (June or July on every row)

Column B contains company names (Company 1 (three rows), Company 2 (8 rows), etc.)

Column C contains workforce hours (decimal numbers)

I've tried replacing all the zeros with nulls before grouping them, as you suggested. It returns the following error: 

DataFormat.Error: We couldn't convert to Number.
Details:
-

Is there a chance that the instances in July where there are no hours yet at all return hours?

 

Thanks again.

 
Posted : 12/07/2020 9:35 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Adam,

I can't reproduce the issue. When you have confidential data you can create another file with dummy data or remove/replace the confidential information to anonymise it.

I have had to create a dummy file in order to try and follow the issue and reproduce it. You can see in the attached that the zero values are not causing a problem.

Mynda

 
Posted : 12/07/2020 8:33 pm
(@catalinb)
Posts: 1937
Member Admin
 

Hi Adam,

Basically, the answer is already in the error message:

DataFormat.Error: We couldn't convert to Number. Details: -

That means you don't have zero's in some cells, you have text "-", so if you simply replace "-" with null, before replacing 0 with null, will work.

 
Posted : 13/07/2020 11:00 pm
(@patri0t82)
Posts: 43
Trusted Member
Topic starter
 

Thank you both for the help. Can't believe I missed what was staring right at me - Catalin, great suggestion. I can't convert "-" to null or zero, as it's a non-numeric value. From looking around on the internet, it seems I just have to let it change to Error and then replace Error with 0. This will still accomplish what I'm after.

Thanks again to both of you.

 
Posted : 14/07/2020 10:39 am
Share: