Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mendoncart
New Contributor III

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?

1 Solution

Accepted Solutions
mendoncart
New Contributor III

Re: Show excluded values in pivot table

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

7 Replies

Re: Show excluded values in pivot table

Sample data please !

mendoncart
New Contributor III

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?

jason_michaelid
Honored Contributor II

Re: Show excluded values in pivot table

As you "Last Sale" expression try something like:

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

See if that works,

Jason

mendoncart
New Contributor III

Re: Show excluded values in pivot table

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_michaelid
Honored Contributor II

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.

Not applicable

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"

Limits_dimensions_properties.png

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
New Contributor III

Re: Show excluded values in pivot table

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

Community Browser