Count Records

caffe2004

Hi Alteryx Community,

Just started using designer and would greatly appreciate any help with this task that I have spent hours with no respite!

I'm looking to feed the customer's name, car# and volume as input in the spreadsheet and get the below 4 columns as output:

1. Unique customer's name

2. Unique Car#

3. Sum volume of unique customer's names corresponding to unique Car#

4. Count of Cars, but don't want to count any two cars with the same customer's name and car# and volume net to zero.

Below is your reference for what I'm trying to do.

I used the summarize tool to group by customer and car# and sum of volume and count by car#, but I got "3" as car count instead of "1".

Please advise how to not count above highlighted rows where the volume net to zero in Alteryx. Thanks in advance for any help.

DataNath

Hey @caffe2004 you're very close - here's how you can achieve the desired fields:

1) We'll need to apply a Formula expression of something along these lines to convert your volume field into numeric and turn bracketed negatives into -x format:

2) We can then Summarize, using 'Group By' [Name] and [Car#]. The difference between your approach is that for the [Car Count], we need to use a Count Distinct - a standard count will just do a general count of how many there are, rather than how many types/different cars there are, which in this case is 1. The volume will now sum as expected:

Hope this helps to get you started!

If your [Volume] field is already fine and the bracketed appearance above is just how you're displaying it in Excel then you can ignore step 1 and just skip to step 2 and use that Summarize configuration!

Summarize car count and volume.yxmd