topic Re: problem with sum in nested expression in QlikView

problem with sum in nested expression

Tue, 13 Nov 2012 10:14:48 GMT

Im having a problem with the sum in a expression. My goal is to sum the total value but to do that I have to add up diffrent set expression. I think it´s in this step the error occur. Maybe the expression should be written in another way, then please explain how this can be done, thanks.

Attache a qvd. Please set year to 2012 and month to nov and INSATS to 7. I also have include some textfield in the qvd to try to explain the problem

Edit: The expression simplyfied are as follow

=((

sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>}
Kvantitet_Minuter)* 4.33 /30.31 /2 *(count(DISTINCT Datum))

+

sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>}
Kvantitet_Minuter/30.31) *count(DISTINCT Datum

)))

/60

And with the data in the qvd it should be something as this - surly wrong with the ()

sum(80 * 4.33) /30.31 /2 *30

sum((80 /30.31) *30))

/60

Re: problem with sum in nested expression

robert99 — Tue, 13 Nov 2012 10:57:40 GMT

Why do you need both sum and count. Do you want to count distinct datum or sum datum.

I don't think (but maybe Im wrong) you can use both sum and count in one expression like this refer manual start of 20.4

If I need to use both then I sometimes use aggr.

say change

count(DISTINCT Datum

to something like aggr (count(distinct Datum),by fields you want to count by))

or you could obtain the appropriate count total by using count in script grouped by as appropriate

Re: problem with sum in nested expression

Tue, 13 Nov 2012 11:09:48 GMT

first. in the last exampel I was putting 30 insted of 15 in the second part, correct should be

sum(80 * 4.33) /30.31 /2 *30

sum((80 /30.31) * 15))

/60

The aim of the count(datum) ie count(date) is to multiply the sum value with number of days in selected period.

The value "Kvantitet_Minuter" ie time in min can be set in three diffrent units, min per day, min per week or min per month. I have to recalculate that value to min per singel day and then multiply it with number of days selected.

The FROM and TO period can also be part of a month so for thoose cases I also need to convert to time per day and the multiply with number of days for that period, even if the user select a hole month the period can be ie 15 days. And if the value are 100 min per month I need to convert it to days and then multiply with 15.

Should I explain in a better way or is´t understandable?

Re: problem with sum in nested expression

Tue, 13 Nov 2012 11:16:05 GMT

I tryed

=
((

sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31) *aggr (count(distinct Datum),BESTNR))

+

(sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>} Kvantitet_Minuter)* 4.33 /30.31 /2 *aggr (count(distinct Datum),BESTNR

)))

/60

It gives me right result for each BESTNR, but no total sum

If I change BESTNR to antoher dimension it stills gives me wrong total sum.

Re: problem with sum in nested expression

robert99 — Tue, 13 Nov 2012 11:18:09 GMT

Hopefully someone else can help you out (Im still learning and have no time at present)

But when I have done similar I have either used

-aggr (to get the appropriate count total) or

-calculated the appropriate count total in script first

But this may not work for you

Re: problem with sum in nested expression

robert99 — Tue, 13 Nov 2012 11:24:43 GMT

"It gives me right result for each BESTNR, but no total sum"

If you take out the aggr expression and change to 1 say. Does it give the right sum based on 1

Re: problem with sum in nested expression

Tue, 13 Nov 2012 11:54:07 GMT

When changed the aggr to 1 it gives right result so my problem seems to be in that part. Thanks for the help. Hopefully someone else can help me for here on.

How can I replace * (1) with *count(date) in the following expression so the total sum works?

=
(

(sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31) * (1))
+
(sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>} Kvantitet_Minuter)* 4.33 /30.31 /2 * (1))

)
/60

Re: problem with sum in nested expression

robert99 — Tue, 13 Nov 2012 12:16:41 GMT

Strange

aggr (count(distinct Datum),BESTNR)

gives the right result and

=((sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31) * (1))

does too based on 1

then when you replace the last 1 with aggr (count(distinct Datum),BESTNR) it should work I think

hopefully someone can give you a working solution as I would like to know what's wrong

You could calculate the date count in script

Re: problem with sum in nested expression

Tue, 13 Nov 2012 12:43:14 GMT

Thanks for the help with the aggr function

I had done wrong with the ().

the correct expression are as

(sum(aggr(sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31), BESTNR) * aggr(count(DISTINCT Datum), BESTNR)
)/60)
+
(sum(aggr(sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>}Kvantitet_Minuter * 4.33 /30.31 /2), BESTNR) * aggr(count(DISTINCT Datum), BESTNR)
)/60)