set analysis using field as filter value

[#1]

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?

[Miguel_Angel_Baeyens]

Try using

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

[#3]

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.

[johnw · Accepted solution]

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

[#5]

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