2 Replies Latest reply: Mar 1, 2016 10:50 AM by Hemang Dave RSS

    Display zero values in multiple columns in a Table

    Hemang Dave

      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.


      sales analysis table_2.PNG


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


      sales analysis table.PNG

      Thanks in advance for your help.


        • Re: Display zero values in multiple columns in a Table
          Stefan Wühl

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

            • Re: Display zero values in multiple columns in a Table
              Hemang Dave

              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.