Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Edge
New Contributor III

Set Analysis with Sum and AGGR function not working

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?

2 Solutions

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Set Analysis with Sum and AGGR function not working

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.


talk is cheap, supply exceeds demand

View solution in original post

Partner
Partner

Re: Set Analysis with Sum and AGGR function not working

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.

View solution in original post

9 Replies
MVP & Luminary
MVP & Luminary

Re: Set Analysis with Sum and AGGR function not working

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.


talk is cheap, supply exceeds demand

View solution in original post

Edge
New Contributor III

Re: Set Analysis with Sum and AGGR function not working

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
New Contributor III

Re: Set Analysis with Sum and AGGR function not working

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
Valued Contributor II

Re: Set Analysis with Sum and AGGR function not working

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

Partner
Partner

Re: Set Analysis with Sum and AGGR function not working

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.

View solution in original post

anushree1
Valued Contributor II

Re: Set Analysis with Sum and AGGR function not working

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

Partner
Partner

Re: Set Analysis with Sum and AGGR function not working

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
New Contributor III

Re: Set Analysis with Sum and AGGR function not working

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
New Contributor III

Re: Set Analysis with Sum and AGGR function not working

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.