topic Re: How do include ALL dimension values even if they are null in Aggr? in QlikView

How do include ALL dimension values even if they are null in Aggr?

lblumenfeld — Fri, 30 Oct 2015 05:42:41 GMT

I am calculating the total budgeted hours for each person per month. In the statement below,

Sum(Aggr( If(Sum([Estimate Hours]) < 120, 1, 0), Resource, Month))

I'm counting the number of months where each resource had < 120 hours budgeted. This works fine for the months in which there is a forecast but ignores the months in which there is no forecast.

So, for example, if Month has values 1,2,3,4,5,6 defined and Joe has the following allocation (month, hours)

1, 100

2, 80

3, 160

then the result will be 2. It should be 5, because there was no forecast for months 4, 5, and 6.

How can I achieve this without adding a zero allocation for the missing months? I already have a table that defines all the months.

Thanks!

Re: How do include ALL dimension values even if they are null in Aggr?

swuehl — Fri, 30 Oct 2015 19:38:25 GMT

Try maybe

Sum( 6- Aggr( If(Sum([Estimate Hours]) >= 120, 1, 0), Resource, Month))

Sum( Aggr(Count(DISTINCT TOTAL Month)- Sum( Aggr( If(Sum([Estimated Hours]) >= 120, 1, 0), Resource, Month)), Resource))

Re: How do include ALL dimension values even if they are null in Aggr?

lblumenfeld — Fri, 30 Oct 2015 21:40:23 GMT

I tried that and it gave the incorrect count. I was able to figure it out. The expression below works.

Count(DISTINCT TOTAL Month) - Alt(Sum(Aggr( If(Sum([Estimated Hours]) >= 120, 1, 0), Resource, Month)), 0)

Re: How do include ALL dimension values even if they are null in Aggr?

prabhu0505 — Fri, 30 Oct 2015 21:53:38 GMT

Try this..

Sum(Aggr( If(Sum([Estimate Hours]) < 120, 1, 0)+NullCount(Sum([Estimate Hours])), Resource, Month))

Re: How do include ALL dimension values even if they are null in Aggr?

swuehl — Sat, 31 Oct 2015 19:37:51 GMT

Have you tried my first or second solution?

The first will not work, but the second should. And the second should also give a total value across all Resources (e.g. when used in a text box), while I think your solution will only work on the Resource's dimension line or when you select a single Resource.

Can you show a sample where my second solution returns incorrect values?