topic Re: Re: sum expression in QlikView

sum expression

realpixel — Thu, 23 Jan 2014 16:27:27 GMT

Hello,

I have following chart.

Nbre TA and Nbre T are an expression

I want to have in the field “RES” the result under

Someone have an idea to have the following result

18,75 = (3/16)*100

25= (4/16)*100

56,25=(9/16)*100

ZP	Nbre TA	Nbre T	CAM	LOC	RES
ZP	2	3	ALCL	-	18,75
ZP	3	4	SOCO	SOCO	25
ZP	5	9	SOCO	-	56,25
	10	16			100

Re: sum expression

Nicole-Smith — Thu, 23 Jan 2014 16:32:51 GMT

Expression would be:

=[Nbre T]/sum(total [Nbre T])*100

Example file also attached.

Re: sum expression

alexandros17 — Thu, 23 Jan 2014 16:33:28 GMT

The expression for RES field is:

sum(NbreT) / sum(TOTAL NbreT) * 100

Hope it helps

Re: sum expression

Thu, 23 Jan 2014 17:12:44 GMT

Hi,

[Nbre T] is an expression, this makes Sum(TOTAL not work in the given formula.

Could you give us the formula of [Nbre T] and the dimensions of your chart? It seems the best would be to adapt this formula to get the total...

Re: sum expression

realpixel — Thu, 23 Jan 2014 18:12:18 GMT

Thank you all for replies, you are right Pierre, the total sum does not work with tese expressions.

Find under the formula for NbreT

count(distinct if( ISO='20', HMCONT)) + (count(distinct if( ISO='40', HMCONT))*2)

dimensions are CAM and ZP

Re: sum expression

Nicole-Smith — Thu, 23 Jan 2014 18:15:34 GMT

count(distinct if( ISO='20', HMCONT)) + (count(distinct if( ISO='40', HMCONT))*2) can be written a bit better:

count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2)

Then the RES expression would be:

count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2) /

sum(aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),CAM,ZP)) * 100

Re: sum expression

realpixel — Thu, 23 Jan 2014 18:35:28 GMT

The expression gives me in RES column, the value under

RES

67.67

52,00

89.98

The data are wrong

It will be

18,75

56,25

Re: sum expression

Nicole-Smith — Thu, 23 Jan 2014 18:38:18 GMT

I think I forgot the TOTAL keyword:

count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2) /

sum(total aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),CAM,ZP)) * 100

Re: sum expression

realpixel — Thu, 23 Jan 2014 18:43:52 GMT

I try this expression but the result is always false

RES:

1.55

2,55

3,21

Re: sum expression

Nicole-Smith — Thu, 23 Jan 2014 18:44:54 GMT

Can you just put this in the chart and tell me what it is returning (once with the total and once without):

1. sum(aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),CAM,ZP))

2. sum(total aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),CAM,ZP))

Re: sum expression

realpixel — Thu, 23 Jan 2014 18:51:54 GMT

The first expression returns, same value of NbreT

The second expression returns

362

Re: Re: sum expression

Nicole-Smith — Thu, 23 Jan 2014 19:02:10 GMT

In your original post, you also have LOC field. I think this also needs to be included in your aggr() and I was missing a set of parentheses on my other expressions:

(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2))/

sum(total aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),ZP,CAM,LOC))*100

I have also attached an example .qvw.

Re: sum expression

Thu, 23 Jan 2014 19:30:04 GMT

You can create a variable for the Nbre T expression. Call it vNbreT then in your RES expression use the following equation

=([Nbre T]/$(vNbreT))*100

This way the variable vNbreT will always be looking at the total of the Nbre T expression (16 in your example) creating an absolute reference to the total and the expression Nbre T will look at the pieces (3, then 4, then 9) of the total giving you the results you are looking for.

Re: sum expression

realpixel — Fri, 24 Jan 2014 08:05:59 GMT

Hello,

1 - I try expression

(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2))/
sum(total aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),ZP,CAM,LOC))*100

But the result is always false, the result

0,828

1,104

2.486

If I use only a part of expression

sum(total aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),ZP,CAM,LOC))*100

The result is 36200 and not 1600 like example_2.qvw

2 - I try to create a variable vNbreT with expresionn contents in NbreT, like that

count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2)

But the result of =([Nbre T]/$(vNbreT))*100 gives me also a false value

500

400

1700

1600

Re: sum expression

Nicole-Smith — Fri, 24 Jan 2014 13:54:49 GMT

I think if you want help you need to post a .qvw

Preparing examples for Upload - Reduction and Data Scrambling

Re: sum expression

realpixel — Fri, 24 Jan 2014 14:58:52 GMT

Hello Nicole,

I understand where does the problem.

I created an inline table to have only the "ZP" and in the demission ZP I have enable the option "Supress When value is Null" to have only the value "ZP"

If I disable option "Supress When value is Null" on "ZP" the total is 362

if I enable option "Supress When value is Null" on "ZP" the total is 16

The expression under does not ignores null values and takes the total amount (362)

sum(total aggr(count({<ISO={'20'}>}distinct HMCONT) + (count({<ISO={'40'}>}distinct HMCONT)*2),ZP,CAM,LOC))*100

There is a possibility with this expression to ignore null value if the option "Supress When value is Null" is enable on ZP dimenssion?

Re: Re: sum expression

Nicole-Smith — Fri, 24 Jan 2014 15:06:31 GMT

The following will make sure there is something in the ZP field (makes sure the length is at least one character):

sum(total aggr(count({<ISO={'20'},ZP={'?*'}>}distinct HMCONT) + (count({<ISO={'40'},ZP={'?*'}>}distinct HMCONT)*2),ZP,CAM,LOC))*100

Re: sum expression

realpixel — Fri, 24 Jan 2014 15:13:30 GMT

Well done, it works perfectly now