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
Solved! Go to Solution.
Hi
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.
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
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
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.
Thanks Andrew! It worked