Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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.
@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
Hi Lisa
I used the first expression and it worked perfectly for me...thank you 🙂