Hi,
I'm currently building a report to track the billing cycles for my business. We have multiple customers with more than one billing cycle, and I would like to only capture the customers that have all their billing cycles closed, and exclude the ones that have 1 or more open cycles. The data looks like this:
bill cycle | customer | status |
1 | abc | closed |
2 | abc | open |
3 | abc | closed |
1 | xyz | closed |
1 | lmno | closed |
3 | xyz | Closed |
I want to get it to exclude any customers that still have open cycles.
bill cycle | customer | status |
1 | xyz | closed |
3 | xyz | closed |
1 | lmno | closed |
What would be the best approach here? I know that cross tab wouldnt be helpful because the values I need (status) arent numerical. I cant really filter out the closed status's because that would still include the customers with open cycles, and I only want the ones who have all of their cycles closed.
Thanks,
May
Solved! Go to Solution.
@maygross One way of doing this with the multi-row tool
There may be another way to do it, but what I did was first, normalize the data so "closed" was the same as "Closed." Then I used a Summarize tool to group by customer and status then another Summarize tool to group by customer and concatenate statuses. Next I used a Filter tool to only allow records to pass if they do not have the word "Open" in the concatenated status. Last thing is I joined back to the original data on customer and got what you're looking for.