Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

set analysis using field as filter value

Hi all,

I have a set analysis expression which looks like this:

=sum({$<[Spend] = {'Mix'}, [Quarter] = {DataPeriod} >} [Spend Amt])

The expression works if I do not have the second part, the "Quarter Key" The "DataPeriod" is a field value which is supposed to return string such as "Jun-2009". But the expression does not work. If I enter "Jun-2009" manually, then it works. I tried the variable expansion using the "$", but that did not work. Any suggestions?



1 Solution

Accepted Solutions
MVP
MVP

set analysis using field as filter value

Seems likely that something is going wrong with how it is interpreting DataPeriod. When I'm trying to debug set analysis expressions like this, I find it helpful to create a straight table with the expression. The caption for the table will then show what you typed in, while the column heading will show how QlikView interpreted it. If you still see DataTable in the column heading, you know it treated it as a literal. If you see 39965, then it figured out that Jun-2009 is a date, but has it in numeric format, so you would need to format the result to match the Quarter. And so on.

Here's my guess:

sum({<[Spend]={'Mix'},[Quarter]={'$(=only(DataPeriod))'}>} [Spend Amt])

View solution in original post

4 Replies

set analysis using field as filter value

Try using

=sum({$<[Spend] = {'Mix'}, [Quarter] = {$(#=MonthName(Date(DataPeriod)))} >} [Spend Amt])


Not applicable

set analysis using field as filter value

I am afraid that is not going to work. The Quarter is a string like "Jun-2009", and the the DataPeriod field returns the same string.

MVP
MVP

set analysis using field as filter value

Seems likely that something is going wrong with how it is interpreting DataPeriod. When I'm trying to debug set analysis expressions like this, I find it helpful to create a straight table with the expression. The caption for the table will then show what you typed in, while the column heading will show how QlikView interpreted it. If you still see DataTable in the column heading, you know it treated it as a literal. If you see 39965, then it figured out that Jun-2009 is a date, but has it in numeric format, so you would need to format the result to match the Quarter. And so on.

Here's my guess:

sum({<[Spend]={'Mix'},[Quarter]={'$(=only(DataPeriod))'}>} [Spend Amt])

View solution in original post

Not applicable

set analysis using field as filter value

Would have taken me a long time to get to this.... It worked! Thank you so much. Yes