Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
aagyemang
Contributor II
Contributor II

Converting Sum IF Statement to Count IF

Hello,

I have ran into a roadblock with creating a calculated field in Qlik Sense. I have a report where I am trying to compare the difference between project actual hours by project budget hours by facility.

Below you can see the expression I made for this report:

If((Alt((Sum(If([Facility]='North',[Total Hours],

          If([Facility]='East',([Total Hours]))))-sum([Admin FTE Hours]))/

                    (sum([Initiative IT Budget: App Hrs])+sum([Initiative IT Budget: Infra Hrs])),0))

                                   <=1.1,'Met','Not Met')

The above calculated field worked when representing the data in table, but when I try and convert the data into a combo chart the data is not accurate.

I have also tried to represent this data in a combo chart as a percentage of total projects using the below expression, and the data is still not accurate. It either returns a 0 or a % a lot lower than the correct answer. I have used a calculated field similar to this before without using the ALT function and it worked fine. Can someone provide me with any suggestions.

If((Alt((Sum(If([Facility]='North',[Total Hours],

          If([Facility]='East',([Total Hours]))))-sum([Admin FTE Hours]))/

                    (sum([Initiative IT Budget: App Hrs])+sum([Initiative IT Budget: Infra Hrs])),0))

                                   <=1.1,1,0)/count([Project Ref Num])

Thanks,

Adwoa

0 Replies