topic count is missing some values in QlikView

count is missing some values

amien — Sun, 18 Sep 2011 19:50:31 GMT

i have this pivot:

dimension: material,material_type

expression:

=sum({$< Month = {"> $(=Max(Month))"} ,Year = {">= $(=Year - 1)"}>} value1-value2)+

sum({$< Month = {"<= $(=Max(Month))"}>} value1-value2)

when i select a certain material_type ,.. this will give me 509 rows for that specific material type.

now i want to create a new pivot .. with only 1 dimension and count instead of sum.

dimension: material_type

expression:

=COUNT(AGGR(

sum({$< Month = {"> $(=Max(Month))"} ,Year = {">= $(=Year - 1)"}>} value1-value2)+

sum({$< Month = {"<= $(=Max(Month))"}>} value1-value2)

)

,Material,material_type)

)

is this not the right approach? i'm missing some rows. the end result is 478 instead of 509

count is missing some values

amien — Mon, 19 Sep 2011 13:08:10 GMT

problem seems to arise when doing a sum+sum

count is missing some values

Mon, 19 Sep 2011 13:30:49 GMT

hi Amien,

have you tried with NODISTINCT clause in the aggr function?

C u,

Stefano.

count is missing some values

amien — Mon, 19 Sep 2011 14:07:06 GMT

thanks for your reply .. no doesn't work

i have this simplified expression:

SUM({$< Month = {"> $(=Max(Month))"} ,Year = {">= $(=Year - 1)"}>} counter) +

SUM({$< Month = {"<= $(=Max(Month))"}>} counter)

dimension is material. I need a count on these results.

count is missing some values

Mon, 19 Sep 2011 14:29:17 GMT

May you attach a file with that table,

i try to make an example and it works, maybe your problem is more complex.

Stefano.

count is missing some values

swuehl — Mon, 19 Sep 2011 14:29:41 GMT

Amien,

if you do a sum(aggr(...)) instead of the count, do you get the same results as for the total sums in your first pivot?

Would it be possible that you post a sample here in the forum?

If you think that the problem arise from using two sums (could you explain that a bit more?), I think you could try rebuilding your expression also:

It seems to me that you are using two sums to get around the Month reset at year change, you probably want to sum or count all records that fall inbetween a date range max(Month) of selected year (you don't need a max(Year) here?) and one year back, right?

I think you could get an expression using only one sum and one set expression if you would use a continouus Date or at least a YearMonth for the date range, do you have something like this in your data model?

Regards,

Stefan

count is missing some values

amien — Mon, 19 Sep 2011 14:42:32 GMT

see first post for example. when i export the pivot to excel, it will give me 509 rows.

i need that value in a pivot for each materialtype

count is missing some values

swuehl — Mon, 19 Sep 2011 16:43:20 GMT

Amien,

I still think you should consider using a Date for the Monthstart, maybe created in your load script using something like

LOAD

...

Makedate(Year, Month, 1) as MonthStart,

...

from ...;

Then you should be able to use one sum and one set expression to calculate your counter

=COUNT(AGGR(

sum(

{$< MonthStart = {">$(=addmonths(Max(MonthStart),-12))<=$(=max(MonthStart))"} ,Year =, Month= >}

value1-value2)

)

,Material,material_type)

)

Regards,

Stefan