Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

apparently simple set analysis failing

Hello all...

The following piece of set analysis expression does not produce a value...

=if ([Academic Year] = ComparatorYear
,
Sum(Total_Target_Sep)
,
Sum({1<[Academic Year]={$(=Only(ComparatorYear))}>} Total_Enrolments))

Where [Academic Year] (from my data set) and ComparatorYear (selection from listbox) contain text values ('2010/11', '2011/12', '2012/13' etc).

Now, if I substitute 1 and zero for the two sums, the output confirms that the academic and comparators either match or don’t depending on selections. If I just substitute the second sum with zero, the output is either the sum of targets or zero as expected. But when I include the second sum, neither sums are produced.

Curiously, this next example works spot on – the difference being that in this case the academic_year_text and comparatorYear2 are in the form 2010, 2011, 2012 etc.

=if(Academic_year_text = ComparatorYear2
,
sum(Total_Target_Sep)
,
Sum({1<Academic_year_text={$(=only(ComparatorYear2))}>} Total_Enrolments))

Can anyone see anything obviously wrong or tell me that I'm doing it the wrong way?

Cheers,

Geoff.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try enclosing the set modifier list values in single quotes to prevent from evaluating the math expression 2012 / 13 etc.:

=if ([Academic Year] = ComparatorYear
,
Sum(Total_Target_Sep)
,
Sum({1<[Academic Year]={'$(=Only(ComparatorYear))'}>} Total_Enrolments))

View solution in original post

3 Replies
jonathandienst
Partner
Partner

Hi

Only(ComparatorYear) will return a null if more than one ComparatorYear value is possible.  I think this is what you need:

=If([Academic Year] = ComparatorYear

,Sum(Total_Target_Sep)

,Sum({1<[Academic Year] = P(ComparatorYear)}>} Total_Enrolments))

Assumes that AcademicYear and ComparatorYear in the same format.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swuehl
MVP
MVP

Try enclosing the set modifier list values in single quotes to prevent from evaluating the math expression 2012 / 13 etc.:

=if ([Academic Year] = ComparatorYear
,
Sum(Total_Target_Sep)
,
Sum({1<[Academic Year]={'$(=Only(ComparatorYear))'}>} Total_Enrolments))

View solution in original post

Not applicable
Author

Thats sorted it thank you. Not sure when I would have realised the expression was trying to resolve 2012 divided by 2013.

Thanks for all your replies