Count if expression

[lbrosten]

I want to count the number of distinct claims and their total $ by status "Closed", "open" , "Reopen", and "All" (which is open and reopen) 

Case	Claim number	Status	indemnity Res	expense Res	indemnity Paid	expense paid
100	100-1	Closed			1000	50
100	100-2	Closed			500	100
100	100-3	Closed			600	60
200	200-1	Reopen	200	50
200	200-2	Reopen	2000	350
300	300-1	Open	1000	350
300	300-2	Open	1500	75
400	400-1	closed			2000	500
500	500-1	Open	1250	50
600	600-1	Closed			550	50
700	700-1	Open	3000	75
800	800-1	Reopen	4000	550

The answers would be as follows but I don't know what expression should be.

Master Items		Expression???
Open Claim Count	4
Open Claim Indemnity Res	6750
Open Claim Expense Res	550
Open Claim total Res	7300
ReOpen Claim Count	3
REOpen Claim Indemnity Res	6200
REOpen Claim Expense Res	950
REOpen Claim total Res	7150
All Open Claim count	7
All Open Claim Indemnity Res	12950
All Open Claim Expense Res	1500
All Open Claim total Res	14450
Closed Claim count	4
Closed Claim Indemnity Paid	4650
Closed Claim Expense Paid	760
Closed Claim total Reserve Paid	5410

[Lisa_P]

Master Items		Expression???
Open Claim Count	4	Count({<Status={Open}>} [Claim number])
Open Claim Indemnity Res	6750	Sum({<Status={Open}>}[indemnity Res])
Open Claim Expense Res	550	Sum({<Status={Open}>}[expense Res])
Open Claim total Res	7300	Sum({<Status={Open}>}[expense Res]) + Sum({<Status={Open}>}[indemnity Res])

Hopefully you get the idea from here

[lbrosten]

Lisa,

The expression Count({<Status={Open}>}[Claim number]) did not work. It threw: "error in set expression"

FYI I am using Qlik Sense Enterprise if that makes a difference.

[Kushal_Chawda]

@lbrosten  If you want to create a output  same as mentioned format then try below

Create a table 

Dimension: 

=ValueList(
'Open Claim Count',
'Open Claim Indemnity Res',
'Open Claim Expense Res',
'Open Claim total Res',
'Blank1',
'ReOpen Claim Count',
'REOpen Claim Indemnity Res',
'REOpen Claim Expense Res',
'REOpen Claim total Res',
'Blank2',
'All Open Claim count',
'All Open Claim Indemnity Res',
'All Open Claim Expense Res',
'All Open Claim total Res',
'Blank3',
'Closed Claim count',
'Closed Claim Indemnity Paid',
'Closed Claim Expense Paid',
'Closed Claim total Reserve Paid')

Measure: 

=Pick(match(ValueList(
'Open Claim Count',
'Open Claim Indemnity Res',
'Open Claim Expense Res',
'Open Claim total Res',
'Blank1',
'ReOpen Claim Count',
'REOpen Claim Indemnity Res',
'REOpen Claim Expense Res',
'REOpen Claim total Res',
'Blank2',
'All Open Claim count',
'All Open Claim Indemnity Res',
'All Open Claim Expense Res',
'All Open Claim total Res',
'Blank3',
'Closed Claim count',
'Closed Claim Indemnity Paid',
'Closed Claim Expense Paid',
'Closed Claim total Reserve Paid'),

'Open Claim Count',
'Open Claim Indemnity Res',
'Open Claim Expense Res',
'Open Claim total Res',
'Blank1',
'ReOpen Claim Count',
'REOpen Claim Indemnity Res',
'REOpen Claim Expense Res',
'REOpen Claim total Res',
'Blank2',
'All Open Claim count',
'All Open Claim Indemnity Res',
'All Open Claim Expense Res',
'All Open Claim total Res',
'Blank3',
'Closed Claim count',
'Closed Claim Indemnity Paid',
'Closed Claim Expense Paid',
'Closed Claim total Reserve Paid'),


count({<Status={'Open'}>}[Claim number]),
sum({<Status={'Open'}>}[indemnity Res]),
sum({<Status={'Open'}>}[expense Res]),
sum({<Status={'Open'}>}[indemnity Res])+sum({<Status={'Open'}>}[expense Res]),
'Blank1',
count({<Status={'Reopen'}>}[Claim number]),
sum({<Status={'Reopen'}>}[indemnity Res]),
sum({<Status={'Reopen'}>}[expense Res]),
sum({<Status={'Reopen'}>}[indemnity Res])+sum({<Status={'Reopen'}>}[expense Res]),
'Blank2',
count({<Status={'Reopen','Open'}>}[Claim number]),
sum({<Status={'Reopen','Open'}>}[indemnity Res]),
sum({<Status={'Reopen','Open'}>}[expense Res]),
sum({<Status={'Reopen','Open'}>}[indemnity Res])+sum({<Status={'Reopen','Open'}>}[expense Res]),
'Blank3',
count({<Status={'Closed'}>}[Claim number]),
sum({<Status={'Closed'}>}[indemnity Paid]),
sum({<Status={'Closed'}>}[expense paid]),
sum({<Status={'Closed'}>}[indemnity Paid])+sum({<Status={'Closed'}>}[expense paid]))

Go to Dimension properties, in Text color expression, put below expression

=if(wildmatch(valueList(
'Open Claim Count',
'Open Claim Indemnity Res',
'Open Claim Expense Res',
'Open Claim total Res',
'Blank1',
'ReOpen Claim Count',
'REOpen Claim Indemnity Res',
'REOpen Claim Expense Res',
'REOpen Claim total Res',
'Blank2',
'All Open Claim count',
'All Open Claim Indemnity Res',
'All Open Claim Expense Res',
'All Open Claim total Res',
'Blank3',
'Closed Claim count',
'Closed Claim Indemnity Paid',
'Closed Claim Expense Paid',
'Closed Claim total Reserve Paid'),'*Blank*'),white())

Go to measure properties, in text color expression, put same expression used in dimension text color expresion

[ToddE]

Hi Lisa

I used the first expression and it worked perfectly for me...thank you 🙂