topic Re: Pivot Table - Include values for dimension in QlikView

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

mazacini — Wed, 21 Sep 2011 11:08:02 GMT

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?

Pivot Table - Include values for dimension

Wed, 21 Sep 2011 12:52:09 GMT

uncheck "Suppress zero" is ok.

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

Regards

Gilles

Pivot Table - Include values for dimension

mazacini — Wed, 21 Sep 2011 12:56:25 GMT

Yes (on the Dimensions tab?)

Pivot Table - Include values for dimension

SunilChauhan — Wed, 21 Sep 2011 13:13:19 GMT

try this

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

Pivot Table - Include values for dimension

mazacini — Wed, 21 Sep 2011 14:29:07 GMT

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

Pivot Table - Include values for dimension

SunilChauhan — Wed, 21 Sep 2011 14:35:20 GMT

i am understand fully but you may reuire linke this

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

Re: Pivot Table - Include values for dimension

mazacini — Wed, 21 Sep 2011 17:38:03 GMT

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?

Pivot Table - Include values for dimension

SunilChauhan — Wed, 21 Sep 2011 19:20:54 GMT

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

Re: Pivot Table - Include values for dimension

mazacini — Thu, 22 Sep 2011 16:13:13 GMT

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

Pivot Table - Include values for dimension

Jason_Michaelides — Thu, 22 Sep 2011 16:20:25 GMT

Can you post an image of your data model?

Re: Pivot Table - Include values for dimension

mazacini — Thu, 22 Sep 2011 16:28:02 GMT

Hi Jason

Do you mean from the table viewer?

Joe

Pivot Table - Include values for dimension

Jason_Michaelides — Thu, 22 Sep 2011 16:29:08 GMT

Yep.

Re: Pivot Table - Include values for dimension

mazacini — Thu, 22 Sep 2011 16:54:49 GMT

Sorry for delay. Give me a few moments while I tidy up the model!

Re: Pivot Table - Include values for dimension

mazacini — Thu, 22 Sep 2011 17:47:43 GMT

Hi Jason

Here is data model.

My Pivot Table has Dimensions Items2.ITEMCODE and Trans1.TR.MTH. (Different from field names quoted above).

I have entered a few expressions based on Sunil's advice, but as it stands my expression is:

sum( if( isnull(TR.VALUE)=0,TR.VALUE,0)).

This seems to work (ie it shows items even if no transaction on Trans1 table).

Also works when I apply filter for TargetExists field in Items2 table.

What I want to be able to do is say, select the ACCCODE (in Trans1 and CUSTS) and say CATCODE (in STOCKCATS and Items 2), and show ALL Items with selected CATCODE, whether they have been sold to selected ACCCODE or Not!

I hope I have explained properly?

Pivot Table - Include values for dimension

Jason_Michaelides — Thu, 22 Sep 2011 18:15:07 GMT

The problem is the only connection between ACCCODE and ITEMCODE is via the transaction table. So you want to select an ACCCODE value and it shouldn't have any impact on the pivot table, right?

If so then

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

might work.

Let me know!

Pivot Table - Include values for dimension

mazacini — Thu, 22 Sep 2011 18:50:45 GMT

Hi Jason

Thanks for sticking with this. Unfortunatley, when I enter the formula, all cells compute to '-' (null?).

I think you may have hit on the problem in your last posting.

I think I'll try another solution, using SET Analsysis. I'm not very good at that, so you might be able to help.

If I can show all the TR.VALUE per ACCCODE for ITEMCODES filtered on TargetExists and CATCODE per TR.MTH (which is a simple filtered Pivot), I know there is some way using set analysis to show ITEMCODES for the TargetExists and CATCODES filter which DO NOT have a record in Trans1, and therefore DO NOT have a TR.VALUE.

Again, thanks for help so far.

Joe

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

mazacini — Thu, 22 Sep 2011 19:22:29 GMT

Hi Jason

To expand on my last posting...

I have set up my pivot which shows sum of TR.VALUE per month, and to which I can apply filters as normal.

I have also set up a straight table showing ITEMCODE and DESC, with SET Analysis to always filter on a particular value of TargetExists (="y"). For comvenience, in order to be able to complete the table, I have a COUNT expresssion.

Now, I need to extend the SET Analysis so that it will show the excluded ITEMCODE and DESC, but always for TargetExists = "Y".

Don;t worry if you can't give this any more time. If I don't hear back, I'll set up a new discussion, which is probably appropriate anyway.

Joe

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

Jason_Michaelides — Thu, 22 Sep 2011 19:26:46 GMT

Can you post your app here?

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

mazacini — Thu, 22 Sep 2011 19:33:41 GMT

Hi Jason

Never did that before. Contains client data. I am worried about security?

Joe

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

Jason_Michaelides — Thu, 22 Sep 2011 19:40:21 GMT

Can you muddle it up so it's not recognisable?

I'll have another look at this in the morning (with or without the app) as my brain's gone to sleep now...