Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
problem seems to arise when doing a sum+sum
hi Amien,
have you tried with NODISTINCT clause in the aggr function?
C u,
Stefano.
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.
May you attach a file with that table,
i try to make an example and it works, maybe your problem is more complex.
Stefano.
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
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
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