20 Replies Latest reply: Sep 22, 2016 4:19 AM by Paul Steinborn

Set Analysis Sum({1<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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

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

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

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

See this sample -

Set NULLINTERPRET = '';

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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

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

'+' 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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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

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

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<DIM>}KPI) not equal Sum({\$<DIM=P()>}KPI) if nothing selected

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

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

I solved this by using:

if(getselectedcount(DIM)>=1,

Sum({1<DIM=p(DIM)>}KPI),

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

)