Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
uncheck "Suppress zero" is ok.
have you unchecked "Suppress null" on both CODE and TRANMTH ?
Regards
Gilles
Yes (on the Dimensions tab?)
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
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
i am understand fully but you may reuire linke this
sum({<Filtername={"Filtervalue"}>} if( isnull(salefield)=0,salefield,0)
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?
sum( if( isnull(salefield)=0,salefield,0)) this will help u
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".
Can you post an image of your data model?