topic Re: Set Analysis with Sum and AGGR function not working in QlikView

Set Analysis with Sum and AGGR function not working

Edge — Sun, 30 Jun 2019 08:36:17 GMT

Hey everyone,

I have the below expression which is not working because I think the set analysis expression in the outer sum "[Semester Year Name]=," is not being considered in the internal aggr when there is a selection on [Semester Year Name].

=num(sum({$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>}aggr([Credit Hour], Enrolment.EMPLID, [Course Number], SemesterNO, Location, [Course College], [Academic Career])),'#,##0')

How do I add a set analysis for the internal aggr function? I tried moving the sum expression to the aggr function but it didn't work. I tried having the sum and the set analysis inside the aggr function but that also didn't work. any ideas?

Re: Set Analysis with Sum and AGGR function not working

Gysbert_Wassenaar — Sun, 30 Jun 2019 15:43:54 GMT

Putting the set analysis inside the aggr function should work.

I see that you're not using an aggregation function inside the aggr function: aggr([Credit Hour], ....
That means that implicitly the Only() function is used as the aggregation function. If is more than one [Credit Hour] value exists per combination of the dimensions you use in the aggr function then Only() function will return null. Perhaps you want to specifify an aggregation function explicitly. Maybe the sum function: aggr(SUM([Credit Hour]), ....
Or the Avg, Min or Max function. Whatever is appropriate in your case.

Re: Set Analysis with Sum and AGGR function not working

Edge — Mon, 01 Jul 2019 05:32:40 GMT

Hey Gysbert,

Many thanks for your response. I tried having the set analysis inside the aggr function but qv gives me an error.

Kindly see attached screenshot. I am not sure what do you mean when you talk about the only() function. I am not even sure what is the only() function.

All I know about the aggr function that it will return one number of credit hours removing duplicates for the dimensions I am aggregating. Is this correct?

Re: Set Analysis with Sum and AGGR function not working

Edge — Mon, 01 Jul 2019 05:35:41 GMT

Hi Again,

I tried adding the set analysis inside the aggr function and updated the object even though QV was giving me an error.

It seems that QV totally ignores the set analysis inside the aggr function.

Re: Set Analysis with Sum and AGGR function not working

anushree1 — Mon, 01 Jul 2019 06:05:08 GMT

Please try:

num(aggr(sum({$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>}[Credit Hour])
Enrolment.EMPLID, [Course Number], SemesterNO, Location, [Course College], [Academic Career]),'#,##0')

Re: Set Analysis with Sum and AGGR function not working

jaibau1993 — Mon, 01 Jul 2019 06:09:55 GMT

Hi!

I think Gysbert meant you to do something like

num(

sum({$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>}

aggr(Only( {$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>} [Credit Hour]),

Enrolment.EMPLID, [Course Number], SemesterNO, Location, [Course College], [Academic Career]))

,'#,##0')

num(

sum({$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>}

aggr(Max( {$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>} [Credit Hour]),

Enrolment.EMPLID, [Course Number], SemesterNO, Location, [Course College], [Academic Career]))

,'#,##0')

You should use an aggregation function inside the AGGR too (Only by default, max, count, sum...) with its own set analysis expression.

Regards,

Jaime.

Re: Set Analysis with Sum and AGGR function not working

anushree1 — Mon, 01 Jul 2019 06:12:46 GMT

Use this please:

num(aggr(sum({$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>}[Credit Hour]),
Enrolment.EMPLID, [Course Number], SemesterNO, Location, [Course College], [Academic Career]),'#,##0')

Re: Set Analysis with Sum and AGGR function not working

jaibau1993 — Mon, 01 Jul 2019 06:16:55 GMT

On the other hand:

You use an AGGR with [Credit Hour] and the you Sum it. Given the linear property of the sum, isnt your expression equivalent to:

num(sum({$<[Enrolled Date]={'$(=Date(today()-365))'},[Semester Year Name]=,SemesterNO={$(=vCombined_Adm_Progn_Max_SemesterNO_Last_year)}>} [Credit Hour]),'#,##0')

With no AGGR function?

Regards,

Jaime

Re: Set Analysis with Sum and AGGR function not working

Edge — Mon, 01 Jul 2019 07:58:45 GMT

Hi Jaime,

First many thanks to all who responded. I am testing all options now.

The reason the previous developer used aggr is:

I think from what I understood from previous comments in this thread because aggr applies the only function by default.

So she gets one CHS value for that combination of dimension and hence duplicate records have no impact on the sum.

So your option won't work for sure.

Re: Set Analysis with Sum and AGGR function not working

Edge — Mon, 01 Jul 2019 11:16:16 GMT

Hey everyone,

Many thanks to all who volunteered and helped with answering this question. I have marked the answers which worked for me. Many thanks for both.

But I have realized something weird though. That if I don't have the set analysis in the outer sum() and the max() or only() function inside aggr(), then the expression won't work.