topic Set analysis not retaining total in QlikView

Set analysis not retaining total

Wed, 15 Feb 2012 14:53:54 GMT

Hi all

I have a number of properties and can create a percentage of current selection / total properties

=sum(PropertyIDCounter)/sum({1}PropertyIDCounter)

I also have a bookmark that filters the total properties into a specific group and I want to work out the percentage based on this bookmark

=sum(PropertyIDCounter)/sum({$*BM05}PropertyIDCounter)

However, when I apply a filter to the data the sum({$*BM05}PropertyIDCounter) also reduces

How do I apply the {1} effect in the first expression that retains the full count of the bookmark regardless of subsequent applied filters

Thanks in advance

John

Set analysis not retaining total

CELAMBARASAN — Wed, 15 Feb 2012 14:56:03 GMT

Hi,

Try using with the TOTAL keyword.

=sum(PropertyIDCounter)/sum(Total {$*BM05}PropertyIDCounter)

Celambarasan

Set analysis not retaining total

Wed, 15 Feb 2012 16:20:45 GMT

Hi Celambarasan

Thanks but it still reduces the total when filters are applied to the dashboard

I thought it might be something to do with ticking the 'apply bookmarch on top of current selection' tick box but I still get the same outcome

John

Set analysis not retaining total

CELAMBARASAN — Thu, 16 Feb 2012 04:39:02 GMT

Hi,

I think you need to apply bookmark for both .

=sum({$*BM05}PropertyIDCounter)/sum(Total {$*BM05}PropertyIDCounter)

Celambarasan

Set analysis not retaining total

Thu, 16 Feb 2012 09:52:09 GMT

Sorry that does not fix it

The problem appears to be in the Bookmark part of the expression

If no filters are applied

=sum({$*BM05}PropertyIDCounter) returns the correct quantity ie: as if the bookmark was applied

However as soon as filters are applied the sum({$*BM05}PropertyIDCounter) amount reduces

Set analysis not retaining total

CELAMBARASAN — Thu, 16 Feb 2012 10:00:11 GMT

Hi,

FRom this which one your expecting

=sum({$+BM05}PropertyIDCounter)/sum(Total {$+BM05}PropertyIDCounter) this gives bookmark selection + current selection results.

=sum({$*BM05}PropertyIDCounter)/sum(Total {$*BM05}PropertyIDCounter) this gives common in both bookmark selection and current selection results.

=sum({BM05}PropertyIDCounter)/sum(Total {BM05}PropertyIDCounter) this gives only the bookmark selection results.

Celambarasan

Set analysis not retaining total

Thu, 16 Feb 2012 12:01:37 GMT

I have a simple QVW file that demonstrates the issue

I do not see a way to attach it other than starting a new discussion - is that correct?

John

Set analysis not retaining total

Thu, 16 Feb 2012 12:10:58 GMT

Got it

just took out the $*

=sum(PropertyIDCounter)/sum(Total{BM05}PropertyIDCounter)

Thanks for your help

John