Qlik Community

Healthcare User Group

Announcements
Submit your remarkable customer story for the Global Transformation Awards at QlikWorld Denver 2022. SUBMIT A STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
clemmytang
Contributor III
Contributor III

SUM SET ANALYSIS WITH DISTINCT VARIABLE

Hi,

I am working on writing an expression that pulls in the $ for the current year, based on episode number.  However, since episode number is repetitive on multiple  claim lines, I would want the expression to be distinct on the episode number.

CY TOTAL PAID:

=SUM({$<[Report Year] = {'Current Year'}>} TOTAL_ALLOWED)


CY TOTAL EPISODES
:

=COUNT({$<[Report Year] = {'Current Year'}>} DISTINCT episode_num)

I have the expressions laid out, but realized the CY TOTAL PAID value is not distinct based on episode number, so the $ is triple it's value.  How would I make the SUM of the SET ANALYSIS in CY TOTAL PAID to be SUMMING based on DISTINCT episode number only?

Please advise.

Thank you!
Clem

 

1 Reply
rmuchhal
Luminary
Luminary

tough to say without all the data and seeing the data model, but you may need something like the below. Look up the aggr function. 

 

=SUM({$<[Report Year] = {'Current Year'}>} 

aggr( sum({$<[Report Year] = {'Current Year'}>} TOTAL_ALLOWED),episode_num)

)

 

-Rishi

Rishi Muchhala
Nemours Childrens
Mgr, Enterprise Intelligence
rmuchhal@nemours.org