
Re: problem with sum in nested expression
Robert Hutchings Nov 13, 2012 6:07 AM (in response to Göran Hofstedt)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
Göran Hofstedt Nov 13, 2012 6:09 AM (in response to Robert Hutchings)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
Göran Hofstedt Nov 13, 2012 6:16 AM (in response to Göran Hofstedt)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)))
/60It 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
Robert Hutchings Nov 13, 2012 6:24 AM (in response to Göran Hofstedt)"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
Robert Hutchings Nov 13, 2012 6:18 AM (in response to Göran Hofstedt)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
Göran Hofstedt Nov 13, 2012 6:54 AM (in response to Robert Hutchings)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
Robert Hutchings Nov 13, 2012 7:18 AM (in response to Göran Hofstedt)Strange
If
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
Göran Hofstedt Nov 13, 2012 7:43 AM (in response to Robert Hutchings)Hi
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)




