4 Replies Latest reply: Oct 3, 2013 5:31 AM by Manish Kachhia

# Need help with formula

Good morning all,

I need help with one of my formulas.

I am using the following formula to calculate excess hours.

sum({<[Date]=,Month=,Year=>}if([Date]<=vMaxDate,if([Product Type]<>'5',[Total Value])))

But with one of our companies (CompanyKey '66') I need to include ProductType '5' in the excess hours calculation.

But for all the other companies I need to exclude Product Type 5.

At the moment, my formula above works perfectly for all companies, except for the company where the CompanyKey is 66.

Can anyone please advice how I can amend my formula, so that only when the CompanyKey is 66 should it include ProductType 5's.

I did try to amend the formula, but then it includes 5 for all companies.

Thanks

*R*

• ###### Re: Need help with formula

Rentia

sum({<[Date]={'<=\$(vMaxDate)'}, Month=, Year, [Product Type] -= {'5'}, CompanyKey -= {'66'}>} [Total Value]) +

sum({<[Date]={'<=\$(vMaxDate)'}, Month=, Year, CompanyKey = {'66'}>} [Total Value])

Hope that helps

Jonathan

• ###### Re: Need help with formula

Hi Jonathan,

I tried the above formula but it gives me zero values back for all companies.

Thanks

*R*

• ###### Re: Need help with formula

Hi Rentia,

I would put the Companykey / ProductType construction in the script:

If(Companykey = 66, [Total value],

If(ProductType = 5,0,[Total Value]))      as Hours

Hours is the resulting field you can use in your Sum.

Michiel

• ###### Re: Need help with formula

Try This....

=sum({<Month = , Year = ,[Company Key] = {'66'} , [Date] = {"<=\$(=vMaxDate)"}>}[Total Value])

+

=sum({<Month = , Year = , [Company Key]  -= {'66'} , [Product Type] -= {‘5'} ,[Date] = {"<=\$(=vMaxDate)"}>} [Total Value])