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?

[mazacini]

Hi Jason

Do you mean from the table viewer?

Joe

[Jason_Michaelides]

Yep.

[mazacini]

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

[mazacini]

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?

[Jason_Michaelides]

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!

[mazacini]

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

[mazacini]

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

[Jason_Michaelides]

Can you post your app here?

[mazacini]

Hi Jason

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

Joe

[Jason_Michaelides]

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