Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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*
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
Hi Jonathan,
I tried the above formula but it gives me zero values back for all companies.
Thanks
*R*
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
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])