Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
clemmytang
Contributor II
Contributor II

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
Kush
MVP
MVP

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
Kush
MVP
MVP

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

clemmytang
Contributor II
Contributor II

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

Kush
MVP
MVP

Glad that helpful