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?

[#2]

uncheck "Suppress zero" is ok.

have you unchecked "Suppress null" on both CODE and TRANMTH ?

Regards

Gilles

[mazacini]

Yes (on the Dimensions tab?)

[SunilChauhan]

try this

if( isnull(salefield)=0,salefield,0) in Place of sales field in expression

or

if( len(salefield)<>0,salefield,0) in Place of sales field in expression

[mazacini]

Hi

My expression is sum(LINEVAL). So I have replaced "salefield" above with "SUM(LINEVAL)".

The Pivot know shows all the CODES!

Thank you!

You might be able to help me further though! When I apply filters, the report will exclude the CODES not associated to the filter. Is there a way I can extend your solution to overcome this?

For instance, I have a Product Group table that has ITEMCODE and PG. When I filter on a particular PG, it seems to override your solution? Maybe Set Analysis?

Can I ask

[SunilChauhan]

i am understand fully but you may reuire linke this

sum({<Filtername={"Filtervalue"}>} if( isnull(salefield)=0,salefield,0)

[mazacini]

Do you mean to include a specific Filtername and Filtervalue?

How would I get this to work for ANY combinations of different filter names and values?

[SunilChauhan]

sum( if( isnull(salefield)=0,salefield,0)) this will help u

[mazacini]

Thank you for your help so far.

I think your solution will work, as long as I filter using a field in the ITEMS table. (I have other fields in the table which I do not mention above).

It does not work when I filter using other fields outside the ITEMS table.

Any suggesttions?

PS -  I am using SUM(LINEVAL) in place of "salefield".

[Jason_Michaelides]

Can you post an image of your data model?