topic Set analysis aggregation for a field with multiple values in QlikView

Set analysis aggregation for a field with multiple values

Mon, 13 Feb 2017 16:09:09 GMT

Hi,

I have the following set analysis that works to give student course completion for students having Status_value as 1-Junior

=sum(aggr(count (DISTINCT {$<[StudentId]={"=[Status_Value]='1-Junior'" } >} if ($(vrDateRange), [StudentId]) ),[Course Number], [ResultEnd Date]))

I want to modify the above expression to give me a total count of student course completion across all Status_values. I tried the following but got 0 as result.

=sum(aggr(count (DISTINCT {$<[StudentId]={"=[Status_Value]='1-Junior', '2- Sophomore'" } >} if ($(vrDateRange), [StudentId]) ),[Course Number], [ResultEnd Date]))

Kindly suggest!

Re: Set analysis aggregation for a field with multiple values

vishsaggi — Mon, 13 Feb 2017 16:14:56 GMT

Try this?

=sum(aggr(count (DISTINCT {$<[StudentId]={"=[Status_Value]= { '1-Junior', '2- Sophomore'} " } >} if ($(vrDateRange), [StudentId]) ),[Course Number], [ResultEnd Date]))

Re: Set analysis aggregation for a field with multiple values

Anil_Babu_Samineni — Mon, 13 Feb 2017 16:16:39 GMT

May be try this

=sum(aggr(count (DISTINCT {$<[StudentId] = , [Status_Value] = {'1-Junior', '2- Sophomore'} >} if ($(vrDateRange), [StudentId]) ),[Course Number], [ResultEnd Date]))

=sum(aggr(count (DISTINCT {$<[StudentId]={"=[Status_Value]={'1-Junior', '2- Sophomore'}" } >} if ($(vrDateRange), [StudentId]) ),[Course Number], [ResultEnd Date]))

Re: Set analysis aggregation for a field with multiple values

Mon, 13 Feb 2017 16:20:09 GMT

Tried. Returns 0

Re: Set analysis aggregation for a field with multiple values

Mon, 13 Feb 2017 16:20:44 GMT

First expression does not return correct value. 2nd expression returns 0

Re: Set analysis aggregation for a field with multiple values

sunny_talwar — Mon, 13 Feb 2017 16:23:33 GMT

May be this:

=Sum(Aggr(Count (DISTINCT {$<[StudentId] = {"=Match([Status_Value], '1-Junior', '2- Sophomore')"}>} if ($(vrDateRange), [StudentId])), [Course Number], [ResultEnd Date]))

or this

=Sum(Aggr(Count (DISTINCT {$<[Status_Value] = {'1-Junior', '2- Sophomore'}>} if ($(vrDateRange), [StudentId])), [Course Number], [ResultEnd Date]))

Re: Set analysis aggregation for a field with multiple values

Anil_Babu_Samineni — Mon, 13 Feb 2017 16:25:23 GMT

May be remove "=" and then check

or else use Match function to retrieve the same

Match(Dim,'Value1','Value2')