Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
agigliotti
Partner - Champion
Partner - Champion

Pivot Table - How to show what is not been sold

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

1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion
Author

The only way I found to solve the problem is using 2 distinct clouds inside the .qvw document: one cloud with all tables included the sales transactions table and the other cloud with only dimensions tables (duplicated with different names).

Now I get the expected results in pivot table but it takes a long time to calculate

In the expression I'm using IF statement instead of SET ANALYSIS because of separation of the two clouds.

Can someone help me to improve the object response time ?

Thanks & Regards

Andrea

View solution in original post

11 Replies
bbi_mba_76
Partner - Specialist
Partner - Specialist

Hi,

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

Not applicable

Hello,

Maybe with :

if( sum(Value)>0,

  1,

  0

)

agigliotti
Partner - Champion
Partner - Champion
Author

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

Not applicable

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

agigliotti
Partner - Champion
Partner - Champion
Author

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

Not applicable

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

This will evaluate the expressions regardless to the current selections.

agigliotti
Partner - Champion
Partner - Champion
Author

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).

Not applicable

You can ignore selections for some fields with this syntax :

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

agigliotti
Partner - Champion
Partner - Champion
Author

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 !