Filter problem

[#1]

I'm breaking my head on this filter, i've been trying quite some variants ..

SUM({<[Country Id]="$([Wfc_Country])">} [TsDayTotal Tickets])

where Wfc_country is the dimension of the graph, and Country Id is a field in the tables.

I'm trying to create a automized waterfall, that's why i can't use the normal country id as dimension.

[#2]

Based on some other treads this should be it

SUM({<[Country Id]={'$(=Only(Wfc_Country))'}>} [TsDayTotal Tickets])

But i'm stil getting 0, when i use the expression

SUM({<[Country Id]="B"}>} [TsDayTotal Tickets])

I'm getting values

[#3]

Maybe i don't understand...... if you want use Set Analiysis for filtering within the specified value of Wfc_Country, try this :

SUM( {$<[Country Id] = {$(#=(Wfc_Country))} >} [TsDayTotal Tickets])

Automatically, the expression, calculate the amount for the specified value, according with other selections.

[#4]

Since SUM({<[Country Id]="B"}>} [TsDayTotal Tickets]) works, then the problem comes from your dollar sign expansion. The number one tip to working with dollar sign expansions and Set Analysis is to put your expression into a new table, but don't give the expression a label. When the chart is rendered, the label will be your Set Analysis expression with your dollar sign expansion evaluated. It should show the expression with the value B.

The dollar sign expansion: $(=Only(Wfc_Country)) will give you the selected Wfc_Country when one and only one Wfc_Country is selected. If there are no selections or multiple selections, I believe it returns null. Is that the functionality you want in your application?

One quick thing to check is to replace the single quotes with double quotes around your dollar sign expansion. I never remember which does which, but I fnd myself using the double quotes more.

In this case, you should be able to check that dollar sign expansion using a Text Object. In your Text Object, use the expression:

=Only(Wfc_county)

If you don't see the answer is B, then that explains why your dynamic expression does not work like the one with the harcoded value.

[#5]

No, That's not the wanted behaviour, what i would like to achive is that on each line of my table i'm getting the right Wfc_country so that i can use it in a set_analysis.

But as you see, things are turning out quite different.

The $(=O... results in a blank, although column 2 works fine.

[#6]

Ok, when I select only 1 Wfc_country this works, but i want to be able to evaluate the specified Wfc_country on each line. I'll explain a bit more what i want to create.

The final goal is to create a automized waterfall chart, based on a drilldown, i have quite some things working, but the only missing link is the abillity to filter (set) the real countries with the Wfc_country (WaterFallCountry), so that i don't have a selection on the real country field, but i can loop that wfc_country in the graph an create a set for each dimension element. the dimension element is wfc_country. I have a fixex non existing start country and end country. in between are the real countries.

But ... i can't get the right syntax running.....

[#7 · Accepted solution]

Where does Wfc_country come from? Is it calculated? Why can't you just use country_id?

From the sounds of it, you have Wfc_country as the dimension. Then in your expression, you want to pull the values where Country_Id equals the dimension on that line. The problem is, I don't believe you can reference the current dimension in a Set Analysis expression. This came up a few weeks back as a couple of people were trying to do Set Analysis on dates to get values related to the dimension. I don't recall them coming up with a way to reference the dimension.

What you should do is use the Country_Id as your dimension and then QlikView will take care of only counting values related to the dimension. I assume that didn't work for you which is why you added the Wfc_country field. Why didn't using Country_Id work? If we know why that didn't work, someone may have an alternative solution.

[johnw]

NMiller wrote:I don't believe you can reference the current dimension in a Set Analysis expression.

Right. The set is established once for the entire chart. It is not established once for each row on the chart. The best way to accomplish the desired result is probably to use Country Id as the dimension, like you said. Next best would probably be to connect the fields via the data model. I'm guessing that these need to stay independent, though. In that case, an IF statement should work:

sum(if("Country Id"="Wfc_Country","TsDayTotal Tickets"))

That may well cause a performance problem, but I don't know of any solution to that other than data model changes.