Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
fanninam
Contributor

No sum for expression in pivot chart

I am trying to get this expression to provide the correct sum in a pivot chart.  It shows a sum of zero instead of the correct summed amount.  Any suggestions?

 

=(If(Match(Account,'Labor','Other ODC','Material/CWT'),

(((Sum(If(Account='Subcontracts/CWTs',([Cost Amount])))
+
Sum(If(Account='Subcontracts/CWTs',[Cost Amount],)
)*[Material Overhead])
-
Sum(If(Account='Subcontracts/CWTs',([Cost Amount]))))
* [Site Mgmt]

+

(Sum(
If([CCP Description]='Mfg Labor (Mfg Proj Mgr)',[Cost Amount]*[Mfg Labor (Mfg Proj Mgr)],
If([CCP Description]='Mfg Labor (Mfg Engineer)',[Cost Amount]*[Mfg Labor (Mfg Engineer)],
If([CCP Description]='Mfg Labor (Mfg Assistant)',[Cost Amount]*[Mfg Labor (Mfg Assistant)],
If([CCP Description]='Eng Labor (Program Mgr)',[Cost Amount]*[Eng Labor (Program Mgr)],
If([CCP Description]='Eng Labor (Senior Systems Eng)',[Cost Amount]*[Eng Labor (Senior Systems Eng)],
If([CCP Description]='Eng Labor (Systems Eng)',[Cost Amount]*[Eng Labor (Systems Eng)],
If([CCP Description]='Eng Labor (Senior Eng)',[Cost Amount]*[Eng Labor (Senior Eng)],
If([CCP Description]='Eng Labor (Engineer)',[Cost Amount]*[Eng Labor (Engineer)],
If([CCP Description]='Eng Labor (Senior Sftwr Eng)',[Cost Amount]*[Eng Labor (Senior Sftwr Eng)],
If([CCP Description]='Eng Labor (Software Eng)',[Cost Amount]*[Eng Labor (Software Eng)],
If([CCP Description]='Eng Labor (Proj Mgr/Adm)',[Cost Amount]*[Eng Labor (Proj Mgr/Adm)],
If([CCP Description]='Eng Labor (Associate Eng)',[Cost Amount]*[Eng Labor (Associate Eng)],
If([CCP Description]='Eng Labor (Eng Assistant)',[Cost Amount]*[Eng Labor (Eng Assistant)],
If([CCP Description]='Touch Discrete',0,
If(Account='Other ODC',[Cost Amount],
If(Account='Travel ODC',[Cost Amount],
If(Account='Material/CWT',
[Cost Amount]*[Material Overhead]
))))))))))))))))))

+
Sum(
If((Left([CCP Description],9)='Mfg Labor') or Left([CCP Description],14)='Touch Discrete',
(If([CCP Description]='Mfg Labor (Mfg Proj Mgr)',[Cost Amount]*[Mfg Labor (Mfg Proj Mgr)],
If([CCP Description]='Mfg Labor (Mfg Engineer)',[Cost Amount]*[Mfg Labor (Mfg Engineer)],
If([CCP Description]='Mfg Labor (Mfg Assistant)',[Cost Amount]*[Mfg Labor (Mfg Assistant)],
If([CCP Description]='Touch Discrete',[Cost Amount]*[Touch Discrete],Null()
))))
)*[Mfg Labor Overhead],
If(Left([CCP Description],9)='Eng Labor',
( If([CCP Description]='Eng Labor (Program Mgr)',[Cost Amount]*[Eng Labor (Program Mgr)],
If([CCP Description]='Eng Labor (Senior Systems Eng)',[Cost Amount]*[Eng Labor (Senior Systems Eng)],
If([CCP Description]='Eng Labor (Systems Eng)',[Cost Amount]*[Eng Labor (Systems Eng)],
If([CCP Description]='Eng Labor (Senior Eng)',[Cost Amount]*[Eng Labor (Senior Eng)],
If([CCP Description]='Eng Labor (Engineer)',[Cost Amount]*[Eng Labor (Engineer)],
If([CCP Description]='Eng Labor (Senior Sftwr Eng)',[Cost Amount]*[Eng Labor (Senior Sftwr Eng)],
If([CCP Description]='Eng Labor (Software Eng)',[Cost Amount]*[Eng Labor (Software Eng)],
If([CCP Description]='Eng Labor (Proj Mgr/Adm)',[Cost Amount]*[Eng Labor (Proj Mgr/Adm)],
If([CCP Description]='Eng Labor (Associate Eng)',[Cost Amount]*[Eng Labor (Associate Eng)],
If([CCP Description]='Eng Labor (Eng Assistant)',[Cost Amount]*[Eng Labor (Eng Assistant)],Null()
))))))))))
*[Engr Labor Overhead]),

If(Account='Material/CWT',0,Null(
))))
))* [Site Mgmt]
),sum(If(Match(Account,'Travel ODC','Subcontracts/CWTs'),0))))

Labels (1)