Discussion board where members can get started with QlikView.
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.
if( isnull(salefield)=0,salefield,0) in Place of sales field in expression
if( len(salefield)<>0,salefield,0) in Place of sales field in expression
My expression is sum(LINEVAL). So I have replaced "salefield" above with "SUM(LINEVAL)".
The Pivot know shows all the CODES!
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
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?
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.
PS - I am using SUM(LINEVAL) in place of "salefield".