topic Re: Problem nesting set modifiers with advanced searches in QlikView

Problem nesting set modifiers with advanced searches

gorotman — Wed, 11 Jan 2017 09:17:34 GMT

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

lironbaram — Wed, 11 Jan 2017 10:05:08 GMT

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

sunny_talwar — Wed, 11 Jan 2017 10:26:20 GMT

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)