Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
gorotman
New Contributor II

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

2 Replies
Partner
Partner

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

LOAD Account,

          Customer,

          AutoNumber(Account&Customer) as Key

          ....

FROM ....;

and then try this:

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