PY Set Analysis

[#1]

Hello,

I have this expression in a pivot table to get the previous year sales:

=sum(aggr(sum({<MonthYear,YearMonth={"$(=YearMonth-100)"}>}SalesValue)/100, Product,Region))

it works fine until I select, for example, 2 YearMonth!?

I think it's because there are values that exists in one YearMonth, but not in the other, so their is nulls in the value columns...

Any suggestions?

Regards,

Francisco

[#2]

OK so you do want the multiples then, should be able to get this working with concat I think, maybe just tricky with the syntax of it. Are you able to post up an example app and I can take a look

cheers

Joe

[#3]

Joe,

Here it is.

Note that the expession "Value_PY_Concat" I need something like this:

=sum(aggr(sum({<YearMonth={$(=Concat(Distinct chr(39) & YearMonth-100 & chr(39), ','))}>}Sales), Product,Region))

Thanks a lot,

Francisco.

[#4]

Ah I see, you are using that YearMonth as a dimension also, then no the concat idea won't work, as the only possible value the expression could display, are those for it's own year.

Only way I can think to do this is in the script create a 'Prior year Sales' field, which is against the current 'YearMonth' and sum on that value instead.

i.e

Yeamonth,     Sales,     Priot Year Sales

201410,         100,           85

2013 10,     85,           xxx

etc etc

That or look to introduce a full master calendar dimension table with the required point in time comparison flags you require

Hope that helps

Joe

[#5]

Thanks Joe...

I already did that para created me another problem when I have products not sold in a YearMonth, I cannot do the left join.

I also can't assing 0 to sales, because the DB will be too big!

It's possible to do something like this:

=sum(aggr(sum({<YearMonth={$(=column(3))}>}Sales), Product,Region))

to use an expression value inside the SA.

Thanks again...