Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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*

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan,

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

Thanks

*R*

mvanlutterveld
Partner - Creator II
Partner - Creator II

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
MVP
MVP

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