Set Analysis with Sum and AGGR function not working

[Edge]

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?

[Gysbert_Wassenaar · Accepted solution]

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.

[Edge]

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?

[Edge]

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.

[anushree1]

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')

[jaibau1993 · Accepted solution]

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')

Or

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.

[anushree1]

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')

[jaibau1993]

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

[Edge]

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.

[Edge]

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.