Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
mmwendab
Contributor II

Sum a field based on Distinct other field

Hello Community.

I need to find the Sum of one field after I find the Distinct Count of another field.  I have my Count Set Analysis as follows:

=Count({$<Stage={'Final'},[Date Type]={'VPP Dates'},Scale={'>50'},Track={'Y'},[#of Days Post VPP]={'>=0'}>}DISTINCT Program)

i.e. I am counting the distinct programs that meet this set of criteria. After counting them, I now need to find the sum of [#of Days Post VPP], how can I incorporate that into a Sum of Count expression?

 

any help rendered will be truly appreciated. 

1 Reply

Re: Sum a field based on Distinct other field

Perhaps this?

=Sum({<[#of Days Post VPP]={'>=0'}>} Aggr(Count({$<Stage={'Final'},[Date Type]={'VPP Dates'},Scale={'>50'},Track={'Y'},[#of Days Post VPP]={'>=0'}>}DISTINCT Program), Dimension))

Or

=Sum({<[#of Days Post VPP]={'>=0'}, Stage={'Final'},[Date Type]={'VPP Dates'},Scale={'>50'},Track={'Y'}>} Aggr(Count({$<Stage={'Final'},[Date Type]={'VPP Dates'},Scale={'>50'},Track={'Y'},[#of Days Post VPP]={'>=0'}>}DISTINCT Program), Dimension))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)