count is missing some values

[amien]

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

[amien]

problem seems to arise when doing a sum+sum

[#3]

hi Amien,

have you tried with NODISTINCT clause in the aggr function?

C u,

Stefano.

[amien]

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.

[#5]

May you attach a file with that table,

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

Stefano.

[swuehl]

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

[amien]

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

[swuehl · Accepted solution]

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