topic Re: Show excluded values in pivot table in QlikView

Show excluded values in pivot table

mendoncart — Mon, 16 Jun 2014 13:45:10 GMT

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?

Re: Show excluded values in pivot table

MK_QSL — Mon, 16 Jun 2014 13:51:35 GMT

Sample data please !

Re: Show excluded values in pivot table

mendoncart — Mon, 16 Jun 2014 15:30:40 GMT

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

Jason_Michaelides — Mon, 16 Jun 2014 15:47:17 GMT

As you "Last Sale" expression try something like:

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

See if that works,

Jason

Re: Show excluded values in pivot table

mendoncart — Mon, 16 Jun 2014 16:23:00 GMT

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.

Re: Show excluded values in pivot table

Jason_Michaelides — Tue, 17 Jun 2014 08:19:57 GMT

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

Tue, 17 Jun 2014 08:50:41 GMT

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

Re: Show excluded values in pivot table

mendoncart — Tue, 17 Jun 2014 15:23:02 GMT

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