topic Re: Set Analysis Sum({1<DIM>}KPI) not equal Sum({$<DIM=P()>}KPI) if nothing selected in QlikView

Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Wed, 21 Sep 2016 12:48:27 GMT

Dear Community,

as i am understanding set analysis correctly

Sum({1<DIM>}KPI) should give the same result (if nothing is selected of DIM) like Sum({$<DIM=P()>}KPI)

But Sum({$<DIM=P()>}KPI) shows not the same number, its only nearly (95%) the same as Sum({1<DIM>}KPI) !

Why is this happening, where is my mistake?

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

tresB — Wed, 21 Sep 2016 12:52:41 GMT

I am afraid that you are not understanding it correctly.

Sum({1<DIM>}KPI) - equivalent to Sum({1} KPI)

Sum({$<DIM=P()>}KPI) - equivalent to Sum(KPI) unless there is NULL in DIM

Edit:

The NULL consideration is an important case with p() or any explicit mention in the set of the dimension. I.e. here with p() for DIM, only those values would be considered which are not null.

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

sunny_talwar — Wed, 21 Sep 2016 12:53:35 GMT

The two expressions are totally different

Sum({1<DIM>}KPI) -> Ignore all selections. You are ignoring selection in DIM twice. This is same as Sum({1} KPI)

Sum({$<DIM=P()>}KPI) -> Allow all selections. You are allowing for selection in DIM twice. This is same as Sum(KPI)

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Wed, 21 Sep 2016 12:57:33 GMT

So here is what i need:

1. Give ALWAYS the total sum of KPI OVER DIM, a selection of any field should not effect the sum

2. Give the sum of KPI OVER DIM, a selection of the DIM field should effect the sum

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

sunny_talwar — Wed, 21 Sep 2016 13:00:45 GMT

Is this in a chart? or text box object

1) Sum({1} KPI) or Sum({1} TOTAL <DIM> KPI)

2) Sum({1<DIM = P(DIM)>} KPI) or Sum({1<DIM = $::DIM>} KPI)

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Wed, 21 Sep 2016 13:08:49 GMT

Thanks Sunny for the fast answer, but 2) will not be equal with 1) if DIM is not selected.

We are using a text box object.

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

sunny_talwar — Wed, 21 Sep 2016 13:10:51 GMT

but 2) will not be equal with 1) if DIM is not selected.

Is that a question for me or are you specifying that as a requirement?

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Wed, 21 Sep 2016 13:14:07 GMT

We just tested your proposal and its a result of it. Any idea why this is happening?

Tresco mentioned that p() is not considering NULL values, maybe in fact of this?

How can we solve this?

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

sunny_talwar — Wed, 21 Sep 2016 13:16:24 GMT

May be like this

2) If(GetSelectedCount(DIM) = 0, Sum({1} KPI), Sum({1<DIM = P(DIM)>} KPI))

or this wasn't working?

Sum({1<DIM = $::DIM>} KPI)

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Wed, 21 Sep 2016 13:28:44 GMT

You are using the same set analysis statements that will not match. The question is not how to show the results, but how to get the first set analysis statement have the same result as the second. While

1. Give ALWAYS the total sum of KPI OVER DIM, a selection of any field should not effect the sum

2. Give the sum of KPI OVER DIM, a selection of the DIM field should effect the sum

we have two textboxes one for 1) and one for 2) and if we clear all selections 1 does not equal 2

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Digvijay_Singh — Wed, 21 Sep 2016 13:50:18 GMT

This may avoid nulls in first expression, not sure though..

Sum({1<DIM={"*"}>}KPI)

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Wed, 21 Sep 2016 13:56:29 GMT

Yes this avoids nulls (2=1), but what if i need the opposite so that 2) should include the nulls (1=2)

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Digvijay_Singh — Thu, 22 Sep 2016 04:20:40 GMT

See this sample -

Set NULLINTERPRET = '';

load * inline [

DIM,KPI

1,100

,100

3,100

4,100 ]

Not the perfect way but 2nd expression regards the selections and includes null as well.

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Thu, 22 Sep 2016 06:11:13 GMT

ok, but is there no possibility to do this with set analysis? Set analysis seems powerfull, maybe there is a way to include also null values with P()

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

tresB — Thu, 22 Sep 2016 06:22:22 GMT

I think this works.

=Sum({1<DIM=p(DIM)>+1-<DIM=e(DIM)>}KPI)

However, there should be a neater solution; wait for someone to come up with.

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Digvijay_Singh — Thu, 22 Sep 2016 06:26:56 GMT

Vow! this works, couldn't understand the use of '+1' here, can you pl help! Thanks

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

tresB — Thu, 22 Sep 2016 06:52:48 GMT

'+' is OR/Union operator here.

1-<DIM=e(DIM)> - excludes the excluded values (anyway they are included in the first part of union) but includes NULL.

So, the first part is with all non-null values and the second part of union includes NULL. Hope this helps.

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Digvijay_Singh — Thu, 22 Sep 2016 06:56:27 GMT

Thanks! Didn't know we can use whole set '1' like this as well.

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

Anonymous — Thu, 22 Sep 2016 08:02:39 GMT

Hi tresco, good idea, but this works only if nothing is selected, when selecting something of DIM the result will be wrong. Null values are only interesting if nothing is selected. If selected only the selected is interesting.

Re: Set Analysis Sum({1}KPI) not equal Sum({$}KPI) if nothing selected

tresB — Thu, 22 Sep 2016 08:10:09 GMT

With me it is working even with selection. Could you post a sample that demonstrates the issue?