Distinct count if

[Cindy]

Hey,
Background: I am using running sum to get running total assumption number per customer_. Then compare the running total per customer_ with a parameter to determine which recommendation I need to use. This is working fine when I have customerid in the table. But when I need to provide a summary table, saying how many customers fall in recommendation 1 then the number is wrong (only showing 1 number instead of per customer anymore). Then I am using below calculation but
getting error message: ‘Execution order mismatch: pre filter can’t have pre agg’ with below calculated filed
distinctCountOver({sfdc_customer_id},[{Recommendation Type}],PRE_AGG)

can you please help me out?

[ErikG]

Hi @Cindy
did you tried to use the runningSum with the partition field, for the customer?
BR

[Cindy]

Thanks for the reply.
I tried to add the customer_id in running sum, as below.

Then tried to calculate the no. of customer falls in recommendation_1( {Overall calc Running total no of headcount _test2}<${NoCSM})

But I got this error messages

[ErikG]

can you try to bring the customer in the group by field?

[Cindy]

Thanks. but Then I won’t have the total number of customer_. And all customer_ will have the same running total as we partion by customer id and then have customer id in the rows.

[Kelseykl]

Hi Cindy, I have a workaround that worked for me, and seems to be similar to what you have tried. So, may work for you as well. I tried to adjust the solution with the information you provided. I hope this helps, but please let me know if you have any questions.

1. Create a new calculated field to manipulate the final visual (You can put anything you want between the quotations, but I would recommend “Total”):

2. In my solution I am using a field instead of the parameter (${SuccessMetric}). You may need to play around with the solution to see if a parameter can be used here and still achieve the same result.

3. Create 3 new calculated fields that will act as columns in the final visual, based on the grouping you defined in {Recommendation type}:

4. Lastly, create a summary table to reflect the distinct count of customer ID per grouping defined in step 3:
   1. Add a pivot Table with the following:
      a. Add rows:
      i. Total
      ii. (If you replace the (${SuccessMetric}) parameter with a field in the “running total no. of headcount_t1” calculated field then include that here)
      b. Add Values:
      i. Maintain or Add
      ii. Recommend_allocation_model
      iii. Remove or Keep No Coverage

       c.	For each of the values select the ellipsis next to the field, and set it to “Sum”. This will include the sum of the distinct count of customer ids in the Total

       d.	Adjust the visual to only show the values for the row “Total”:
            i.	Collapse the rows to the “Total”-level, so that it is the only row you can see
           ii.	Under Visual properties, select the eye icon next to “+/- buttons” to hide the expand rows option for users

5. This should lead to the result below. (Values seen below are dummy values)

[Cindy]

Thank you so much for your detailed explaination!

And your comment about the parameter is correct… My parameter in the runningsum is wrong. I’ve updated to
.

By follwing your comment, i can get a summary table, but the number seems wrong. All the customer fall under recommendation type: remove or keep coverage.

.

Would you please help check what’s the issue here?

Below is my new added calculated field for your reference.

Thanks in advance!

[Kelseykl]

I would recommend checking two things:

1. Double check the parameter you are comparing against in {Recommendation type}. If it is too low of a value then all of the values would get sorted under “Remove or Keep No Coverage”.

2. Add {Recommendation type} and {running total no. of headcount_t1} as values to the summary visual, and expand out all the rows to verify that the values are reflecting as you would expect.

[Cindy]

Hi,

I have created 2 tables (sfdc account id as fields) to check the number of customer for maintain or Add option. And It turns out that I have quite a few. I think there is still an issue calculating the running total logic in summary table. Do you have other solutions? Any insights will be much appreciated.

[Cindy]

Hi @Kelseykl

Checking if you have any updates?

[Kelseykl]

Hi @Cindy, sorry it is difficult to see what is happening without being able to see the data. Looking at the running-total in your screen-shot, you may need to order the rows to match the order condition you applied in the running-total calculated field. If I am right, then this should show the running-total increasing row-by-row.