Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
mazacini
Contributor III

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

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?

22 Replies
Highlighted
Not applicable

Pivot Table - Include values for dimension

uncheck "Suppress zero" is ok.

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

Regards

Gilles

Highlighted
mazacini
Contributor III

Pivot Table - Include values for dimension

Yes (on the Dimensions tab?)

Highlighted
SunilChauhan
Esteemed Contributor

Pivot Table - Include values for dimension


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

Highlighted
mazacini
Contributor III

Pivot Table - Include values for dimension

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

Highlighted
SunilChauhan
Esteemed Contributor

Pivot Table - Include values for dimension

i am understand fully but you may reuire linke this

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

Highlighted
mazacini
Contributor III

Re: Pivot Table - Include values for dimension

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?

Highlighted
SunilChauhan
Esteemed Contributor

Pivot Table - Include values for dimension

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

Highlighted
mazacini
Contributor III

Re: Pivot Table - Include values for dimension

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".

Highlighted
Partner
Partner

Pivot Table - Include values for dimension

Can you post an image of your data model?