Total Count of Weeks That Include Blank Weeks

[asmithids]

Hello,

I am having an issue counting the total number of weeks in the Total Utilization column.  The count function is only counting the weeks that have data and is ignoring the weeks that do not have data which is reducing my denominator.  I need to count the total number of weeks as displayed in the pivot table as part of my denominator.  The utilization calculation works per week, but as mentioned the Total Utilization is ignoring the blank weeks. 

I attached a spreadsheet that shows the Total Utilization as calculated by QlikView and the correct utilization side by side.  I also attached the app. 

Thank you in advance for any assistance with this issue.

Alec

[jagan · Accepted solution]

Hi,

Try this expression.

If(RowNo() = 0,

Sum(PlanHours)/(Count(TOTAL DISTINCT WeekEnd)*40),

Sum(PlanHours)/(Count(DISTINCT WeekEnd)*40))

Regards,

Jagan.

[Anonymous]

Hi,

You need to handle the null in your document before loading .

See below : NULL handling in QlikView

Null Value Handling using 3 Value Logic | Qlik Community

[asmithids]

Thank you Jagan.  The only change I needed to make was to use the ColumnNo() function instead of the RowNo().

Thanks again!

[jagan]

Hi Alec,

Good catch, I tried this in Straight table, but in Pivot table you have to use ColumnNo().

Regards,

Jagan.