help with an expression

[#1]

Hi,

I have a table that has Date, PO Categories (1 for hourly or 3 for monthly), billed hrs and rates (hourly or monthly). for instance

Date Billed Hrs PO Categ Rate

01/02/2010 8 1 $100

02/03/2010 8 3 $6000

I calculated total hrs in a month against each line of the table for all the months using monthsstart and monthsend and network function for each month

Now i am using the expression below to calculate the revenue

Revnue = "if(PO_Catg = 3, Sum(billed_hrs*rate/MonthTotalHrs), Sum(billed_hrs*rate))"

but I am not getting correct answer for category 3 that is for monthly billing.

the answer lets say for Jan must be

8*6000/168 = $285.7 but I get some weird figure that is way way too high.

When I check the value of MonthTotalHrs using a text box or table box I get correct value of 168. and if I use a fix value of say 168 in the expression I get the correct answer. I have tried a using a variable for MonthTotalHrs, tried moving the brackets around but nothing seems to work.

Can someone please help?

regards,

Manoj Agrawal

[#2]

Is it possible for you to post a sample version of your application??

[#3]

Hi,

I think there were duplicate records in my calendar table which was causing the problem. However, I fixed it by doing the calculations in the load script.

thanks for the reply!

manoj

[mantaq10]

I think it depends on where you use the expression. How are you calculating the value of MonthTotalHrs or is it a constant or a fixed value for each iteration of the expression?

The best explanation could be that MonthtotalHrs are not adding up for all the month days and thus you are getting higher values.

Share the code for MonthTotalHrs, that would help...

[#5]

Hi Atif,

Like I mentioned in the original post, I used monthsstart, monthsend and network functions to calculate month total hrs in my calendar table for each entry in my fact table. anyway, the problem is solved by calculating during the load instead of in the expression.

thanks for replying!

manoj agrawal

[#6]

Hi Manoj,

Please do check this expression.

sum(if(PO_Catg = 3,([Billed Hrs]*Rate)/MonthTotalHrs, [Billed Hrs]*Rate))

Hope this works fine.

Thanks Joseph....