COUNT(DISTINCT creates An issue has occurred during processing. We... Error

I have created this first beastmode calc, and it seems to work just fine.

Case when `Total On Hand` - SUM(CASE when `Planned Shipment Date` < (CURDATE()+ 21) then `Outstanding Quantity` else 0 end) < 0 then `Item No` else 0 end

When I add a COUNT(DISTINCT to the end, it does not work and gives me the classic "An issue has occurred during processing. We..." error.

COUNT(DISTINCT Case when `Total On Hand` - SUM(CASE when `Planned Shipment Date` < (CURDATE()+ 21) then `Outstanding Quantity` else 0 end) < 0 then `Item No` else 0 end)

I would have expected it to work. Anybody have any suggestions?

Thanks

Find more posts tagged with

Accepted answers

user02631

Learned MySQL and used that instead of trying to do it in BeastMode. Got the results I needed.

Thanks,

All comments

Godiepi

Try This one

COUNT(DISTINCT 
Case when Sum(`Total On Hand`) - SUM(CASE when 
`Planned Shipment Date` < (CURDATE()+ 21)
 then `Outstanding Quantity` else 0 end) < 0 then `Item No`  end)

ST_Superman

try dropping the else 0 off of the end of the case statement

(edit) @Godiepi apparently I should have refreshed my browser before posting my suggestion

user02631

I copied that new statement in and it still gives me the "An issue has occurred during processing" error.

Godiepi

There might be some Nulls that are causing the calculation to break so try adjusting your calculation for those Nulls like

COUNT(DISTINCT 
Case when Sum(`Total On Hand`) - SUM(CASE when 
`Planned Shipment Date` < (CURDATE()+ 21)
 then ifnull(`Outstanding Quantity`,0)  else 0 end) < 0 then `Item No`  end)

ST_Superman

Is this beastmode being used for a summary number? Mind sharing the rest of the chart settings?

user02631

Not sure what you mean by summary, but my guess is that the answer is yes.

A little background. There are a list of items. Each item is assigned to a group.

If you look at the first picture attached, you can see that some items have a negative value in the Try 1 column. What I would like to do is get the number of items with a negative value to appear next to the Count of Parts column in the second picture. Once that is accomplished, we can see how many items are in each group and then how many have a negative value based on the criteria from the earlier posts.

As shown earlier, when I attempt to count the number of negative items via Count(Distincts and Case whens, it errors out.

Let me know what other info you might need.

Thanks for all of the help so far. Dojo 1.PNG Dojo 2.PNG

Godiepi

@user02631

oh that looks a little different from the prior description of what you are trying to do, but focusing for now in the second picture you can have a table card with 3 columns:

1st column = Group

2nd column = Beastmode, lets call it "Count of Negatives"

code would be as simple as

ifnull(Count( Case when `Try 1` < 0 then `Try 1` end),0)

The reason why I am not doing a Count Distinct here is that if there are 2 or more negatives with the same value it would counted once

3rd column = `Try 1` , setting the aggregation to Count and naming the field "Count of Parts"

@ST_-Superman-_ any ideas ?

ST_Superman

@user02631 I think this would be easier if we could get a sample of the data. Would you be able to share a sample of your dataset? Ideally, it would need to contain 2 or 3 groups with several of the items from each group. Feel free to anonymize the names of the items or groups, but without understanding the dataset structure, I think it will be very unlikely that we will get you the correct solution.

user02631

Learned MySQL and used that instead of trying to do it in BeastMode. Got the results I needed.

Thanks,