topic Re: Sum of set analysis in QlikView

Sum of set analysis

Fri, 04 Jul 2014 13:10:24 GMT

Hi,

I've created some variables that I use to calculate a column in a table(graph). The problem I'm facing now is that when I want to show the total value of that entire column in a pie chart, I either get the message: no values (I've got the dutch version so pardon me if the translation of the error isn't correct), when I set the dimension limits to 5 for example I get the error: all values below limit.

The variables I use:

Streefperc, this is a variable that can be set by a slider and can take a value in the range of 0-1. It is used to calculate a percentage.

Streefhuur = 'Streefperc*Maxhuur' (Maxhuur is a field, Streefhuur is, for example, 70% of Maxhuur)

In the graph table I then use as expression Streefhuur-Nettohuur, to calculate the marge between Nettohuur and streefhuur.

When I create a pie chart with the same dimension and the expression: SUM(Streefhuur-Nettohuur) I get the errors.

I've also tried to create a separate variable Marge, which calculated Streefhuur-Nettohuur, and tried the sum of that variable, and even a variable that wàs the sum of the variable marge. Still get the same errors. Also tried to expand the vars, so sum($(=$Marge))) of sum($Marge)). No good. I don't think the dimension is the problem because i've tested with several logical dimension, one consists of three groups, so that shouldn't be a limit problem.

I'm really stuck at this and could use some help. Thanks

Re: Pie chart with variables in the expression

Anonymous — Fri, 04 Jul 2014 13:31:00 GMT

Hi Ruud,

Do you can to show the QVW file ?

Re: Pie chart with variables in the expression

Fri, 04 Jul 2014 13:35:30 GMT

I don't think I can, i still have a personal edition of Qlikview, I'm travelling at the moment but i'll add some clarifying screenshots later this evening

Re: Pie chart with variables in the expression

Fri, 04 Jul 2014 18:36:06 GMT

I've added some screenshots of the script, the second example shows the two tables, I want to project the total marge, which is also shown in the top of the table, in a pie chart. The dimension I use consists of 3 values, district 1, district 2 and district 3. When I use this dimension Qlickview gives the error "no values to be shown" (as in example 2) When I change the dimension limit to only show the highest 3, QV gives the error "all values below limit" (example 3)
Hope this clarifies my problem a little bit more!

Re: Sum of set analysis

Mon, 07 Jul 2014 13:05:15 GMT

Can someone please help me?

Re: Sum of set analysis

martinpohl — Mon, 07 Jul 2014 13:15:53 GMT

in a pie chart you can't show negative values. It is possible that there are negative values as shown in picture 2.

First try another chart type (bar chart)

Re: Sum of set analysis

Mon, 07 Jul 2014 13:20:25 GMT

Bar chart doesn't work either. It's true that "Marge" can have negative values, but I'm interested in the sum, which is positive. At the top of the table it already shows the sum of Marge: 87.951,69. What I want is that the user can see how this total is divided per district.

Re: Sum of set analysis

martinpohl — Mon, 07 Jul 2014 13:41:32 GMT

Can you post a screenshot from the table view?

Re: Sum of set analysis

chematos — Mon, 07 Jul 2014 14:47:11 GMT

Add the dimension Location and use as expression sum(Streefhuur)-sum(Nettohuur), but if there is a negative value in one of the Locations, the pie chart will not be shown

Re: Sum of set analysis

Mon, 07 Jul 2014 14:56:05 GMT

try SUM($(Streefhuur)-Nettohuur)

Re: Sum of set analysis

Mon, 07 Jul 2014 17:30:38 GMT

The strange thing is, when I use this the chart only show 3 negative values.. But your answer got it almost right:

SUM((Streefperc*Maxhuur))-SUM(Nettohuur) Did the trick. Because of some strange reason using the variable Streefhuur (which calculates streefperc*maxhuur) was the bottleneck.

I thank you sir!

Re: Sum of set analysis

Mon, 07 Jul 2014 17:31:05 GMT

I already tried this one, didn't work