2 Replies Latest reply: Jan 11, 2017 5:26 AM by Sunny Talwar

# Problem nesting set modifiers with advanced searches

Hi all,

I have a Pivot table with: customer dimension, account dimension, sum(travel_expenses), sum(account_total).

I want to show rows only when travel expenses is >0, but set modifiers ignoring current row context.

This is the example from guide:

sum( {\$<Customer = {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}>} Sales )

I prepared:

sum( {\$<other_dimensions=, account= {“=Sum({\$<other_dimensions=>} travel_expenses) > 0”}>} account_total)

"other_dimensions=" is to indicate that there are a lot of other dimensions that I omitted to simplify example.

But Qlikview resolve the nested "=Sum({\$<other_dimensions=>} travel_expenses) > 0" for all customers, not for customer in current row.

Therefor it shows customers and accounts, when account total is greater than 0 in total document.

So qlikview ignores current row context (customer).

This is a bug or is impossible to do it?

There is a solution, using advanced searches?

This is a workaround without advanced searches:

if(Sum({\$<other_dimensions=>} travel_expenses) > 0, sum( {\$<other_dimensions=>} travel_expenses) > 0”}>} account_total)

Thank's all

• ###### Re: Problem nesting set modifiers with advanced searches

hi

think of set analysis as a selection in the model

therefore it isn't made on row level but on dimension level in the whole document

if you want row level you'll need to use if function as you suggested

• ###### Re: Problem nesting set modifiers with advanced searches

I think since your chart has two dimensions where you are checking if Sum({\$<other_dimensions=>} travel_expenses) > 0, your set analysis is not working. In order to make advanced search isn't working. With a slight change in the script, you might be able to make it work

Customer,

AutoNumber(Account&Customer) as Key

....

FROM ....;

and then try this:

Sum({\$<other_dimensions=, Key = {“=Sum({\$<other_dimensions=>} travel_expenses) > 0”}>} account_total)