5 Replies Latest reply on Jul 6, 2018 7:59 AM by Zach Ferris

# If/Then Help

I have a crosstab sheet that lists Patient ID followed by all Sales Order Numbers associated with that patient and an LOD Calculation that counts the amount of Item IDs per Sales Order. What I want to do is get an average count of "Item Transitions" per patient. Item Transitions would be defined as when a count of IDs changes from one SO to another. So using my LOD function, I imagine an IF/THEN statement is necessary. There is a date field, which makes me think this could be similar to a moving average table calc, right?

IF [Count of Items per SO] in month one =/= [Count of Items per SO] in month two THEN "1" ELSE "0" END

Something like that, but I imagine there will be a tablecalc to look at the previous SO or previous order delivered and look at the difference.

Then, could I just use the "Grand total" in the analytics tab and summarize using Average instead of Total?

I hope this makes sense. There is a very good chance I'm overthinking it. Any help is appreciated.

• ###### 1. Re: If/Then Help

Hi Zach,

I assume that this is answer you have been looking for! Hope It helps!

Best regards,

Yasin.

• ###### 2. Re: If/Then Help

Not quite yet. What I need is an output like this.

10001     1372679     2     0
1416460     1     1

1491820     3     1

1783672     4     1

1866327     1     1

1941486     1     0

That would show 4 different changes in order counts across sales orders.

I would then take THOSE totals per patient to find the average changes per patient.

• ###### 3. Re: If/Then Help

The 1/0s are to show when the next SO has a different count from the previous SO