Show in pivot when when no values

[#1]

Hi all,

I need to show 2 pivot tables, partially related. One table is of sales and is selectable by invoice date amongst other things. A customer may have special prices which I want to show in another table, but if there are no corresponding sales for the current selections still to show the product but with a zero value.

The attached qvw mocks up what I am trying to achieve (the top half represents no selections, the bottom when a specific date is selected). There are 1,000s of rows in real life though!

Any help appreciated,

Gordon

[#2]

Hi Gordon

I'm not too sure that I've got your requirement correct, but its a simple thing for you to look at, go to the dimensions tab and for each dimension where you want to force it to show everything check the box marked Show All Values.

Let me know if this is not what you are looking for.

[#3]

So can you tell, what has gone wrong when you selected the date in your mock up?

What was the expected result in the special rates table in the second case?

[#4]

Thanks for the replies.

I have 2 source tables; the first of customer/parts for which there are special prices and the second actual sales details. What I want is to always see are the customer/products that have special prices whether there are sales or not. So, for example, if a customer has a special price for a product I still want to see it with a total sales value of 0 if no sales or the actual total of the current selections. So I need to have one pivot table of customer/parts sales that reacts to selections, and the other table the special prices where the customer/products are always shown but the total value reflects the actual value of the current selections.

The lower example in the mock up shows what I am trying to achieve. Short of adding a row to the sales table for every customer/product/date combination.....

Regards,

Gordon

[#5 · Accepted solution]

Good old 'aggr(sum(if' came to the rescue here.

For completeness, the qvw which shows the solution is attached.

Thanks all for your time.

Gordon