7 Replies Latest reply: Jun 17, 2014 11:23 AM by Ruytter Mendonça

# Show excluded values in pivot table

Hi.

I have a straight table with the customers names and the sales value.

I want to make another straight table but only with the customers that didnt have a sales value.

What I have:

Customer IDCustomer Name
1Customer A
2Customer B
3Customer C
4Customer D

Customer NameSales
Customer A10000
Customer D50000

What I want:

Customer NameLast Sale
Customer B50 days ago
Customer D8 days ago

The days ago I can calculate, but, i'm not being able to list who didnt have bought.

Can you guys help me?

• ###### Re: Show excluded values in pivot table

Well, what kind of sample?

Let's say i have a listbox for my customers table.

When I select year 2014 and month June, the possible values remain white and the excluded ones (those who did not bought yet in this selected date) become gray.

What I want, is to list on a straight table, who of my customers still haven't made a purchase.

Did this help?

• ###### Re: Show excluded values in pivot table

As you "Last Sale" expression try something like:

Max({<Customer=E()>} SaleDate)

See if that works,

Jason

• ###### Re: Show excluded values in pivot table

For the last sale date I'm using Max(Date), but the thing is, i only can show all customers by using the checkbox "Show all values" on the Dimension tab of a straight table.

But if I check it, will show ALL customers, and I only want who has no purchases on a selected period.

I've tried to use a calculated dimension with if(aggr(sum(sales),customer)=0,Customer) but returns nothing.

So, the goal is show the excluded values of customers.

• ###### Re: Show excluded values in pivot table

Can you post a sample app?  You should be able to do this in the expression with something similar to my suggestion above.

• ###### Re: Show excluded values in pivot table

Hi,

I test with your sample data you give above.

To have the result you want, in the properties of the table "SIMPLE TABLE", you have to

- uncheck "Suppress Zero-Values" Box in "Presentation" --> It allows to see the customer "B" and "C"

- Put a dimension limit on the Customer name to filter on the customer you want to see.

- Check "Restrict with valuers ..." option

- Use the "Show ongly values that are" Option

with this parameter "Less than or equal to"

and put "0" in the input zone

- Select "Exact amount" option

- unchek "Show others"

Is the result correspond to your need?

if you use "PIVOT TABLE"

you cannot use "Dimension limit" so in the expression you can use a formula like "if(Sum(Sales)=0,1,0)" and

Check the option "Suppress Zero values" in the presentation properties of the table.

The result is the same like above.

Thks.
Arnaud