Display zero values in multiple columns in a Table

[#1]

Hi

I've three columns in a table. Column 1 represents accounts, Column 2 represents Sales% and Column 3 represents Sales Target %.

A salesman is assigned to sell certain products.

Products are sold to clients who hold accounts.

Each account has a sales Account Target associated to it.

Both a client account as well as Target accounts hold products.

Some accounts are sold products that are not part of the target account and vice versa.

The performance of a salesman is measured by the sales he generates for a given account compared to that account's target sales.

The attached qvf file contains the sales analysis illustrating the above example.

The table needs to show following cases.

1. An account has generated >0 sales

2. An account has generated 0 sales

Using set analysis I am able to get the 1st case. But not able to show cases where account has generated 0 sales (case 2).

In the data you'll see account IGV which has generated 0% sales compared to it's target of 0.13% sales. Please help display IGV in the output table.

Following picture displays the current state.

Ultimately it should should be the following i.e. include record IGV.

Thanks in advance for your help.

[swuehl]

IGV is not a possible dimension value given your current selection of effective date and rating_salesman_name.

[#3]

Thanks Stefan

Could you please explain why?

Here is the rationale why IGV should show value under Target Sales%

IGV (account Id 21137) has 3 target accounts associated with it as per table midr_target.

Target Account ID 20609 (Target rank 1)

Target Account ID 24595 (Target rank 2)

Target Account ID 20004 (Target rank 4)

In the set analysis I'm putting the condition target_rank = {'1'}

So the corresponding target account id for IGV would be 20609.

Now the selected salesman (Boris, salemsman_id 12875) is responsible for selling 6 products per product table

product_ids (1886, 27065, 32722, 38179, 38732, 39124)

Out of these products as of 02/24/2016, following products have a target sales value associated with it per the sales_target table

product_id_effective_date2      target_sales_weight      

1886_2016-02-24                           0.0141%

32722_2016-02-24                         0.0306%

38732_2016-02-24                         0.0826%

Therefore the total sales target for IGV = 0.1273% (i.e. the sum of above 3 target_sales_weight)

This demonstrates the case that while IGV (account ID 21137) hasn't generated any sales, IGV's target account (target_account_id 20609) has 3 products with their corresponding sales target amount and therefore the total of those 3 target sales weights (0.1273%) should be showing in the table.