Pivot Table - Include Table 1 Dimensions for Table 2 aggregation even if not present in Table 2

[mazacini]

I have ITEMS table with Dimensions CODE and ITEMCODE. I have a SALES table with TRANSDATE, TRANSMTH,TRANSREF, ITEMCODE and LINEVAL.

I have built a Pivot Table which shows total LINEVALUE for CODE per TRANMTH.

But, I want to show for all CODE, even for CODE where there is no record in the SALES table.

Currently in my Pivot Table, only displays CODE where there is a record in the SALES table.

As far as I can tell, I have unchecked the relevant Suppress Zero and Suppres Null.

Help anyone?

[mazacini]

Hi Jason

App Attached.

2 Tables. Table on the right has SET Analsyis expression which will always list products with 'Target?' field = 'y'.

On Table on the left, when I make any selections, it will include a relevant set of products with 'Target?' field = 'y' (if appropriate to the selection). What I want is to show in the right table those products with 'Target?' field = 'Y'', which are NOT in the table on the left.

Rgds

Joe

[SunilChauhan]

i believe aggr funtion inside aggr function will only work like this

aggr(sum( if( isnull(TR.VALUE)=0,SUM({<ACCCODE= >} TR.VALUE),0),ITEMCODE,ACCCODE)

[mazacini]

Thanks Sunil

Expression computes to '-' for all dimensions.

I think there may be a syntax error, arising between "," and "ITEMCODE".

Rgds

Joe