Show excluded values in pivot table

[mendoncart]

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 ID	Customer Name
1	Customer A
2	Customer B
3	Customer C
4	Customer D

Customer Name	Sales
Customer A	10000
Customer D	50000

What I want:

Customer Name	Last Sale
Customer B	50 days ago
Customer D	8 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?

[MK_QSL]

Sample data please !

[mendoncart]

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?

[Jason_Michaelides]

As you "Last Sale" expression try something like:

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

See if that works,

Jason

[mendoncart]

Janson, thanks for answering my thread.

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.

[Jason_Michaelides]

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

[#7]

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

[mendoncart · Accepted solution]

Ok guys, I made it!

I had to check 'show all values' on dimension tab, then i made a expression that results 0 for those I wanted to hide, and then I checked 'supress zero values' on presentation tab.

I'm posting a little exemple to help anyone !in the future.

Thanks very much for all the help!!! !