2 Replies Latest reply on Jun 21, 2018 10:39 AM by Emily Skoraczewski

# Calculation to count line item parts per case by customer

Hello all,

I am looking for some help on a calculation in my data. I cannot post the workbook because it is proprietary information, but I have some screenshots that I think should help.

What I'm trying to do is create a graph that will show me the number of cases that have at least one Line Item Part Id tied to it.

For example, in the screen shot below. I'm looking to calculate by Dealer ID, the number of cases that have a line item part ID.

What I thought was going to was is to create a calculation that that would count each line item part id, then total up by Dealer ID.

The calculation behind AGG(Cases with Part...) is IF [Parts Count] = 0 THEN 0 ELSE 1 END

Now, when I try roll up this calucation on the Dealer ID level, which would tell me both: the number of their cases that have >=1 Line Item Part ID AND, how many parts per cases, I can't because it's an aggregate function.

Can anyone help me with this? Being able to report how many cases have Line item parts ID is very helpful.

• ###### 1. Re: Calculation to count line item parts per case by customer

Emily,

You can make a calculation using lod that will return a boolean (true if the CaseId have at least one item)

caseHaveAtLeastOneItem =

{FIXED [Case Id]:  MAX(if not isnull( [Line Item Part Id])  then  1 else 0 end )} = 1

so if the item is not null return 1 , then for the Case Id if the Max is 1 then you have at least one item.

then to get the number of cases having at least on item, you can do

COUNTD(if [caseHaveAtLeastOneItem] then CaseId end)

Michel

1 of 1 people found this helpful
• ###### 2. Re: Calculation to count line item parts per case by customer

Thank you for your help! This is EXACTLY what I was trying to do!