Hi, I'm trying to show all the ovedue types and count the number of items for those types even if they're 0 or empty. Unfortunately, when I use the Summarize node, the overdue types without values are excluded. Example below: Input: Overdue TypeItem IDNot OverdueaOverdue Not OverduebNot Overduec Current Output (grouped by overdue type and count using countdistinct): Overdue Type# of ItemsNot Overdue2 Desired Output: Overdue Type# of ItemsNot Overdue2Overdue0 Thanks

Counting all Types including empty ones

Hi,

I'm trying to show all the ovedue types and count the number of items for those types even if they're 0 or empty. Unfortunately, when I use the Summarize node, the overdue types without values are excluded. Example below:

Input:

Overdue Type	Item ID
Not Overdue	a
Overdue
Not Overdue	b
Not Overdue	c

Current Output (grouped by overdue type and count using countdistinct):

Overdue Type	# of Items
Not Overdue	2

Desired Output:

Overdue Type	# of Items
Not Overdue	2
Overdue	0

Thanks

Transformation

Accepted answers

AndrewW

Having multiple projects does change things slightly and MarqueeCrews approach is required, creating all combinations of Project Name and Overdue Type and filling in the gaps with the actual Item counts where available, converting those unavailable (NULL) to 0. Attached workflow does this. Sadly I can't put screen shots into this forum as my local IT have locked down this functionality with this forum software.

HM.yxmd

All comments

AndrewW

I'm going to assume you've made an error with your request and the expected results are:

Overdue Type	# of Items
Not Overdue	3
Overdue	0

Although I can't replicate your results, using the Summarise tool gives me:

Overdue Type	# of Items
Not Overdue	3
Overdue	1

There may be very simple ways around counting the Null, but I don't know them so have approached this in a slightly different way. First I have added a sort, sorting by the Overdue Type and ItemId. Next I added a Multi-Row formula, adding a new field, to convert Nulls to 0 and to put a 1 against each unique Item Id - hence why the sorting is important. The multi row formula expression is: IF IsNull([ItemId]) THEN 0 ELSEIF [ItemId] = [Row-1:ItemId] THEN 0 ELSE 1 ENDIF.

If it's not a distinct count of ItemId that's required then a multi-row formula isn't required, a standard formula could be used to convert all nulls to 0 and all non-nulls to 1 and sum that column.

The results of the count distinct, using the ItemId and Summing the new field are:

Overdue Type	CountDistinct ItemId	Sum New Field
Not Overdue	3	3
Overduw	1	0

Workflow is attached and fingers crossed I haven't misinterpreted your requirements.

CountAllTypes.yxmd

MarqueeCrew

In your example, you have all Overdue Types in the input data. I took an approach to collect the unique types (regardless of counts/items) and used that information to set a default of 0 for each type. I'll then count the non-empty occurrences of item ID's and add them to zero. All Types will appear along with their count.

I hope that this helps you.

See you at inspire16,

Mark

Overdue.yxmd

Thank you all for attempting to solve my problem.

Unfortunately, I forgot to mention that because there is an extra layer of complexity (i.e. projects). For each project there are multiple items, some of which are overdue and some which are not overdue. For Project 2, both the overdue and not overdue types will show since there are items in those types, however, for project 1, there are only items in the not overdue type so if I summarize it by project then by type, the overdue type will not show for project 1 but I do need all the types to show regardless of whether there are items which fall into those types.

Apologies for leaving that piece of information out and I appreciate any help I can get.

Input:

Project Name	Type	Item ID
1	Not Overdue	a
1	Not Overdue	b
2	Not Overdue	c
2	Overdue	d

Current Output (grouped by overdue type and count using countdistinct):

Project Name	Overdue Type	# of Items
1	Not Overdue	2
2	Not Overdue	1
2	Overdue	1

Desired Output:

Project Name	Overdue Type	# of Items
1	Not Overdue	2
1	Overdue	0
2	Not Overdue	1
2	Overdue	1

Thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703