Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
clemmytang
Contributor III
Contributor III

Summing SET ANALYSIS with DISTINCT Variable

Hi,

I am working on writing an expression that pulls in the Total $ 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'}>} DISTINCTepisode_num)

I have the expressions laid out, but realized the CY TOTAL PAID value in the first expression is not distinct based on episode number, so the $ is tripling its 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 Solution

Accepted Solutions
Kushal_Chawda

may be below

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

or

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

View solution in original post

3 Replies
Kushal_Chawda

may be below

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

or

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

clemmytang
Contributor III
Contributor III
Author

Thank you so much, Kush!!

It was the first expression that lined up!

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

Much appreciation,

Clem

Kushal_Chawda

Glad that helpful