Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

20 Replies
tresesco
MVP
MVP

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.

sunny_talwar

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)

Anonymous
Not applicable
Author

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

sunny_talwar

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)

Anonymous
Not applicable
Author

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.

sunny_talwar

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?

Anonymous
Not applicable
Author

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?

sunny_talwar

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)

Anonymous
Not applicable
Author

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


prob.png