Pivot Table - How to show what is not been sold

[agigliotti]

Hello Experts,

Our customer ask us to create a pivot table with customers and product categories (dimensions) with the indication for each customer if that product category has been sold or not.

I just spent a lot of time to achieve it but with no luck.

Can anyone drive me to the right direction ?

Many thanks in advance.

Best Regards

Andrea

[bbi_mba_76]

Hi,

maybe with an expression like sum(if(value>0,1)) ?

[#3]

Hello,

Maybe with :

if( sum(Value)>0,

  1,

  0

)

[agigliotti]

no because I have to display all values of product categories regardless it has been sold or not !

take a look below:

          Product category  categ1     categ2     categ3      categ10

Customers

A                                                      X             X

B                                        X                           X

C                                                                     X

the X symbol means the product category is not been sold to that customer, in this example the "categ3" has not been sold to any customers insted of "categ10" that has been sold to all customers.

I hope now is more clear.

Regards

Andrea

[#5]

If you want to see all data, you can unmark 'Suppress Zero Values' in the presentaton tab (in the properties of your table)

[agigliotti]

I already tried to unmark that option but I still not get the expected results because I see only the product categories that are associated to the current selection.

The current selection is for example:

item group = 03

item line = 01

sales agent = A

sales year = 2013

Where:

"item group" and "item line" are both fields of "Items" table.

"sales agent" and "sales year" are fields of "Sales Transactions" table.

For the above values of "item group" and "item line" I have a total of 35 product categories.

In the pivot table I get only 32 product categories because of the current selections !

I need to see always all values of product categories, ignoring selections made in "sales agent" and "sales year", but at the same time, I want to see only customers associated with those fields.

Can you give me some help ?

Thanks & Regards

Andrea

[#7]

Try with sum({1} Value) instead of sum(Value).

This will evaluate the expressions regardless to the current selections.

[agigliotti]

It doesn't works because I have to ignore only some selections and not all, in this way I get all product categories (155) and all customers (23000) while I need to get only the product category associated with selections made in "item group" and "item line" (35) and only the customers belongs to the selections made in "sales agent" field (95).

[#9]

You can ignore selections for some fields with this syntax :

Sum({<[Sales agent]=, [Sales year]=>} Value)

[agigliotti]

ok in this way I get the right product categories values but I get all customers instead of only those belongs to the sales agent selected.

I attached an example.

If you select the value "AG1" on "Agente" field, you'll see the "CAT4" value in pivot table go away and that's the problem !