Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
MVP
MVP

Re: apparently simple set analysis failing

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

3 Replies
MVP
MVP

Re: apparently simple set analysis failing

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
MVP
MVP

Re: apparently simple set analysis failing

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

Not applicable

Re: apparently simple set analysis failing

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

Community Browser