I am trying to figure out a variable that I can apply to my data. I work for a hospital pharmacy and am trying to find out usage of various as needed medications. I want to know how often it's ordered and if ordered, how many times was it administered. Currently, I have a variable that counts the number of times the order ID with a unique administration time appears, but what I would like to see is a column that shows example: Ordered & Admin ^7 for 7 administrations or Ordered if no administrations. I can't seem to figure out the correct variable formula to achieve this.
Thanks in advance.
This is what I tried to use, but it continues to fail
Current variables in use
Ordered and # of admins =[Ordered w or w/o admins]+" ^ "+[Multiples]
Ordered w or w/o admins =If (IsNull([Admin Date and Time]);"Ordered";"Ordered & Admin")
Multiples =Sum(Count([Order ID])) ForAll([Admin Date and Time])
Unique admin = If(IsNull([Admin Date and Time])) Then "0" Else "1"
Unique order ID =If(Concatenation([Order ID];[Order Date and Time]))=Previous(Concatenation([Order ID];[Order Date and Time]) ) Then 0 Else 1