Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- How to wrap text in the edit expression window?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
chhavi376
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to wrap text in the edit expression window?
Hi,
I have a very huge expression in one of my charts, and i have to edit it now.
The problem is that it is coming in one straight line and I have to scroll it horizontally.
I want it to be on the same page(wrapped) with a vertical scrollbar.
is there any other way except doing it manually?
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
adamdavi3s
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Firstly, sorry for the huge emails everyone has just got 😄
Ok here is my own example:
=round(((pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM([BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM(CNST/If(v_UseMFF=1,[MFF],1)),SUM(if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM([EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM([ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM([HIGH COST DRUGS]),SUM([IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM([MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM(if(v_NCI=1,0,[NON CLINICAL INCOME]/If(v_UseMFF=1,[MFF],1))),SUM([OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER NON IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM([OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM([OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM([PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM([PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM([PROSTHESES/IMPLANTS]),SUM([RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM([THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM([WARD]/If(v_UseMFF=1,[MFF],1)),SUM([DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM([SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM([FCE NUMBER])) - (pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}CNST/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[HIGH COST DRUGS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_NCI=1,0,[NON CLINICAL INCOME]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER NON IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PROSTHESES/IMPLANTS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[WARD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[FCE NUMBER]))),2)
I've put my finally bit at the top so you don't have to read the gumpf below if you don't want.
This took me about 10mins to do all of these tests with the help of google....
Finally I used notepad++ I hit enter at the end of the line and then hit the funny backwards P symbol on the toolbar which shows you the characters (Same as word)
To copy the CRLF I had to click to the left of it and then use shift + right arrow (once) to select it, but it didn't seem to work.
So a quick google later and I found you can do this in notepad++
Edit > Line operations > split lines
Select the line first, resize the window and then use that command and it will split the formula to fit the window
=round(((pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL
STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER
SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY
SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY
COMMISIONING'),SUM([BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM(CNST/If(v_UseMFF=1,[MFF],1)),SUM(if(v_CC=1,0,[CRITICAL
CARE]/If(v_UseMFF=1,[MFF],1))),SUM([EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM([ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM([HIGH COST
DRUGS]),SUM([IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM([MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM(if(v_NCI=1,0,[NON CLINICAL
INCOME]/If(v_UseMFF=1,[MFF],1))),SUM([OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER CLINICAL
STAFF]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER NON
IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER SPECIAL PROCEDURE
SUITES]/If(v_UseMFF=1,[MFF],1)),SUM([OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM([OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM([PATHOLOGY]/If(v_U
seMFF=1,[MFF],1)),SUM([PHARMACY
SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM([PROSTHESES/IMPLANTS]),SUM([RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM([THERAPIES]/If(v_UseMFF=1
,[MFF],1)),SUM([WARD]/If(v_UseMFF=1,[MFF],1)),SUM([DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM([SECONDARY
COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM([FCE NUMBER])) - (pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL
CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER
CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE
SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY
SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}CNST/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[HIGH COST DRUGS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_NCI=1,0,[NON CLINICAL
INCOME]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER
DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER NON
IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PROSTHESES/IMPLANTS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[WARD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[FCE NUMBER]))),2)
One idea, use a JS Beautifier, it gets close but still not great as it will only pick up on } really
= round(((pick(match(COST_TYPE, 'BLOOD', 'CNST', 'CRITICAL CARE', 'EMERGENCY', 'ENDOSCOPY', 'HIGH COST DRUGS', 'IMAGING', 'MEDICAL STAFF', 'NON CLINICAL INCOME', 'OPERATING THEATRES', 'OTHER CLINICAL STAFF', 'OTHER DIAGNOSTICS', 'OTHER NON IDENTIFIABLE', 'OTHER SPECIAL PROCEDURE SUITES', 'OUTPATIENTS', 'OVERHEADS', 'PATHOLOGY', 'PHARMACY SERVICES', 'PROSTHESES/IMPLANTS', 'RADIOTHERAPY', 'THERAPIES', 'WARD', 'DRUGS', 'SECONDARY COMMISIONING'), SUM([BLOOD] / If(v_UseMFF = 1, [MFF], 1)), SUM(CNST / If(v_UseMFF = 1, [MFF], 1)), SUM(
if (v_CC = 1, 0, [CRITICAL CARE] / If(v_UseMFF = 1, [MFF], 1))), SUM([EMERGENCY] / If(v_UseMFF = 1, [MFF], 1)), SUM([ENDOSCOPY] / If(v_UseMFF = 1, [MFF], 1)), SUM([HIGH COST DRUGS]), SUM([IMAGING] / If(v_UseMFF = 1, [MFF], 1)), SUM([MEDICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM(
if (v_NCI = 1, 0, [NON CLINICAL INCOME] / If(v_UseMFF = 1, [MFF], 1))), SUM([OPERATING THEATRES] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER CLINICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER DIAGNOSTICS] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER NON IDENTIFIABLE] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER SPECIAL PROCEDURE SUITES] / If(v_UseMFF = 1, [MFF], 1)), SUM([OUTPATIENTS] / If(v_UseMFF = 1, [MFF], 1)), SUM([OVERHEADS] / If(v_UseMFF = 1, [MFF], 1)), SUM([PATHOLOGY] / If(v_UseMFF = 1, [MFF], 1)), SUM([PHARMACY SERVICES] / If(v_UseMFF = 1, [MFF], 1)), SUM([PROSTHESES / IMPLANTS]), SUM([RADIOTHERAPY] / If(v_UseMFF = 1, [MFF], 1)), SUM([THERAPIES] / If(v_UseMFF = 1, [MFF], 1)), SUM([WARD] / If(v_UseMFF = 1, [MFF], 1)), SUM([DRUGS] / If(v_UseMFF = 1, [MFF], 1)), SUM([SECONDARY COMMISIONING] / If(v_UseMFF = 1, [MFF], 1))) / SUM([FCE NUMBER])) - (pick(match(COST_TYPE, 'BLOOD', 'CNST', 'CRITICAL CARE', 'EMERGENCY', 'ENDOSCOPY', 'HIGH COST DRUGS', 'IMAGING', 'MEDICAL STAFF', 'NON CLINICAL INCOME', 'OPERATING THEATRES', 'OTHER CLINICAL STAFF', 'OTHER DIAGNOSTICS', 'OTHER NON IDENTIFIABLE', 'OTHER SPECIAL PROCEDURE SUITES', 'OUTPATIENTS', 'OVERHEADS', 'PATHOLOGY', 'PHARMACY SERVICES', 'PROSTHESES/IMPLANTS', 'RADIOTHERAPY', 'THERAPIES', 'WARD', 'DRUGS', 'SECONDARY COMMISIONING'), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[BLOOD] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
CNST / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
if (v_CC = 1, 0, [CRITICAL CARE] / If(v_UseMFF = 1, [MFF], 1))), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[EMERGENCY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[ENDOSCOPY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[HIGH COST DRUGS]), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[IMAGING] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[MEDICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
if (v_NCI = 1, 0, [NON CLINICAL INCOME] / If(v_UseMFF = 1, [MFF], 1))), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OPERATING THEATRES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER CLINICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER DIAGNOSTICS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER NON IDENTIFIABLE] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER SPECIAL PROCEDURE SUITES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OUTPATIENTS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OVERHEADS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PATHOLOGY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PHARMACY SERVICES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PROSTHESES / IMPLANTS]), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[RADIOTHERAPY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[THERAPIES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[WARD] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[DRUGS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[SECONDARY COMMISIONING] / If(v_UseMFF = 1, [MFF], 1))) / SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[FCE NUMBER]))), 2)
Then I thought about using an excel beautifier (google excel formula beautifier) and I think this is OK but you'll need to tidy out the 'ARRAY ROW STOP weirdness where it has replaced the } bracket for some reason
=
round(
( ( pick(
match(
COST_TYPE,
'BLOOD',
'CNST',
'CRITICAL CARE',
'EMERGENCY',
'ENDOSCOPY',
'HIGH COST DRUGS',
'IMAGING',
'MEDICAL STAFF',
'NON CLINICAL INCOME',
'OPERATING THEATRES',
'OTHER CLINICAL STAFF',
'OTHER DIAGNOSTICS',
'OTHER NON IDENTIFIABLE',
'OTHER SPECIAL PROCEDURE SUITES',
'OUTPATIENTS',
'OVERHEADS',
'PATHOLOGY',
'PHARMACY SERVICES',
'PROSTHESES/IMPLANTS',
'RADIOTHERAPY',
'THERAPIES',
'WARD',
'DRUGS',
'SECONDARY COMMISIONING'
),
SUM(
[BLOOD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
CNST /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
if(
v_CC = 1,
0,
[CRITICAL CARE] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
[EMERGENCY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[ENDOSCOPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[HIGH COST DRUGS]
),
SUM(
[IMAGING] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[MEDICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
if(
v_NCI = 1,
0,
[NON CLINICAL INCOME] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
[OPERATING THEATRES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER CLINICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER DIAGNOSTICS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER NON IDENTIFIABLE] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER SPECIAL PROCEDURE SUITES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OUTPATIENTS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OVERHEADS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PATHOLOGY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PHARMACY SERVICES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PROSTHESES/IMPLANTS]
),
SUM(
[RADIOTHERAPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[THERAPIES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[WARD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[DRUGS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[SECONDARY COMMISIONING] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
) /
SUM(
[FCE NUMBER]
) ) - ( pick(
match(
COST_TYPE,
'BLOOD',
'CNST',
'CRITICAL CARE',
'EMERGENCY',
'ENDOSCOPY',
'HIGH COST DRUGS',
'IMAGING',
'MEDICAL STAFF',
'NON CLINICAL INCOME',
'OPERATING THEATRES',
'OTHER CLINICAL STAFF',
'OTHER DIAGNOSTICS',
'OTHER NON IDENTIFIABLE',
'OTHER SPECIAL PROCEDURE SUITES',
'OUTPATIENTS',
'OVERHEADS',
'PATHOLOGY',
'PHARMACY SERVICES',
'PROSTHESES/IMPLANTS',
'RADIOTHERAPY',
'THERAPIES',
'WARD',
'DRUGS',
'SECONDARY COMMISIONING'
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [BLOOD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) CNST /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) if(
v_CC = 1,
0,
[CRITICAL CARE] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [EMERGENCY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [ENDOSCOPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [HIGH COST DRUGS]
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [IMAGING] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [MEDICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) if(
v_NCI = 1,
0,
[NON CLINICAL INCOME] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OPERATING THEATRES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER CLINICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER DIAGNOSTICS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER NON IDENTIFIABLE] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER SPECIAL PROCEDURE SUITES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),