topic Re: Total Count of Weeks That Include Blank Weeks in QlikView

Total Count of Weeks That Include Blank Weeks

asmithids — Sun, 23 Nov 2014 21:10:52 GMT

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

Re: Total Count of Weeks That Include Blank Weeks

jagan — Mon, 24 Nov 2014 06:03:58 GMT

Hi,

Try this expression.

If(RowNo() = 0,

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

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

Regards,

Jagan.

Re: Total Count of Weeks That Include Blank Weeks

Anonymous — Mon, 24 Nov 2014 06:12:08 GMT

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

Re: Total Count of Weeks That Include Blank Weeks

asmithids — Mon, 24 Nov 2014 23:29:23 GMT

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

Thanks again!

Re: Total Count of Weeks That Include Blank Weeks

jagan — Tue, 25 Nov 2014 01:54:43 GMT

Hi Alec,

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

Regards,

Jagan.