Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aagyemang
New Contributor

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

Community Browser