Hello everyone!
I have a recurring problem, and it is related to the pivot tables when I add fields to segment data and to be able to look deeper. The issue here, is that as you can see in the image of my table, the sum or 'P1 SUBTOTAL’of the individual values of the Failed + field should be 181 (55+93+3+30) and in this case it is 175.
I have to clarify that there are duplicates for each company_id, so I used distinct_count_if, but there is something clearly wrong as the values do not match when you add them automatically and manually. Do you find the problem solvable?
Hello @Juan, this is an interesting error. I wonder if we just try a different way to retrieve the company_id values you want to focus on and run the distinct_count function seperately, if we can fix the issue you are running into. Let’s split this up into 2 calculated fields.
First, use an ifelse statement to only return the company ids linked with the proper status values. Here you have to options, check if the values don’t equal the options you want to avoid or if they do equal the options you like. I think it is better to check if they equal the options you are looking for so I am assuming they are Failed, Success, or Rejected:
Failed + Success + Rejected = ifelse(status = 'Failed' OR status = 'Rejected' OR status = 'Success', {company_id}, NULL)
Now, you can either try to enter this field directly into your value field well and add the distinct count aggregation on the table, or you can create another calculated field to wrap the result in a distinct_count aggregation. distinct_count({Failed + Success + Rejected})
Let me know if either of those options give you the result you are looking for. Thank you!
Hello @Juan, did my last response help guide you towards your expected output? I will mark it as the solution, but please let me know if you have any further questions and I can guide you further. Thank you!
Hi @DylanM , sorry, i thought it was already mark as a solution, this works! in adition to a simple agreggation of fields, where i concatenated the fields type and plan, problem its solved!
