topic Re: Need help with formula in Water Cooler

Need help with formula

Thu, 03 Oct 2013 07:14:56 GMT

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*

jonathandienst — Thu, 03 Oct 2013 08:39:20 GMT

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

Thu, 03 Oct 2013 09:01:31 GMT

Hi Jonathan,

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

Thanks

*R*

mvanlutterveld — Thu, 03 Oct 2013 09:26:54 GMT

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

MK_QSL — Thu, 03 Oct 2013 09:31:39 GMT

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])