topic `Hide Non-Selected Values, on an Ignore Selections in QlikView

`Hide Non-Selected Values, on an Ignore Selections

jrdunson — Mon, 09 May 2016 12:29:37 GMT

Hello

I have a chart, that accumulates... totaling an amount by year

I want to allow a user to drill into the years... without the user selections to affect the totals.

So, I've gone the route of using set analysis, where I ignore year selects: sum({$<[Year]=>}[Cash Amount])

[If I don't include this, then the totals change... adding up only for the years selected]

This works fine... however, extraneous years still show on the chart... Here is the same chart, with years 2005-2016 selected... As you can see, the non-selected years...still show on the chart... and still accumulate:

Is there a way to hide the years, not selected ...? That is, honor user selections for which years to view , but ignore the years selected for the total amount?

Jarrell

Re: `Hide Non-Selected Values, on an Ignore Selections

sunny_talwar — Mon, 09 May 2016 12:33:24 GMT

Try this:

Sum({$<[Year]=>}[Cash Amount]) * Avg(1)

Here Avg(1) will become 0 forcing your expression to be 0 for years which are not selected. For years that are selected Avg(1) will be 1 and will give the unfiltered output for the expression.

Re: `Hide Non-Selected Values, on an Ignore Selections

swuehl — Mon, 09 May 2016 12:35:06 GMT

There are two options,

a) use advanced aggregation around your current expression, with Year as dimension

b) use an AsOf table

Both approaches are described here:

Calculating rolling n-period totals, averages or other aggregations