0 Replies Latest reply: Oct 31, 2017 1:53 PM by Adwoa Agyemang RSS

    Converting Sum IF Statement to Count IF

    Adwoa Agyemang

      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