Hello,
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
=if([Unique Administered Doses]=“1”, concatenation([Ordered w or w/o admins]+" ^ "+[Multiples]) ; “Ordered”)
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
Switch to standard if then else syntax, it’s easier to use and read then the old method.
Unless you have other fields and breaks in your report that you haven’t shown, the context calculation of your multiples variable has problems too. If you are trying to count the number of times the med was administered per order id, you need to do something along the lines of:
Multiples = ( count( [Admin Date and Time] ; distinct ) in( [Order ID] ) + 0
Then for your concatenation:
If [Multiples] = 0 then “Ordered”
elseif [Multiples] >= 1 then "Ordered & Admin ^ " + [Multiples]
else “Unknown”
This works, but I don’t get the correct output.
=If(Concatenation([Order ID];[Order Date and Time]))=Previous(Concatenation([Order ID];[Order Date and Time]) )
And ([Multiples] = 0 ) Then “”
ElseIf [Multiples] >= 1 Then "Ordered & Admin ^ " + [Multiples]
Else “Ordered - no administrations documented”
I was able to figure out a syntax that worked.
=If [Unique Order IDs]=1 And [Administered Doses]>0 Then ([Ordered w or w/o admins] +[Multiples] + " dose(s)") ElseIf [Administered Doses]=0 Then “Ordered - no administrations documented”
