Allocate Sums to other Values

Wed, 18 Apr 2012 17:10:32 GMT

I am having issues trying to take Margin from certain SalesRep_Id and assigning half of this margin to SalesRep_ID X and the other half to SalesRep_ID X. These allocations are not working for 50/50 split accounts. I have removed other split accounts from my load script and expression to make it easier to read. These other split accounts are included in the screenshots but not in the load script/expressions.

I have two type of splits. Even splits 50/50 and un-even splits xx/xx. I have given an example below of a working uneven split and using the same logic an even split not working.

Load Script

//Defines all salesrep accounts by name
SalesRepMaster:
LOAD * inline [
          last_name, SaleRep
          Cowan75/House25, Cowan
          Hawkinson/House, Hawkinson     
          Cowan75/House25, House
           Hawkinson/House, House
];
//NOTE: Cowan75/House25 salesrep_id = 3449
//NOTE: Hawkinson/House salesrep_id = 3293
//Old Way of Handling split accounts refer to 'Margin' expression below to see how this is used
IF(match(salesrep_id, 
          3293,3449),(extended_price-(commission_cost*qty_shipped))) AS Spilt5050Account_Margin,
//No Spilt Accounts 
IF(match(salesrep_id, 
          1017, 1018,1022,1023,1024,3201,3202),
          (extended_price-(commission_cost*qty_shipped))) AS NonSpilt_Margin,
//Margin Caculation
(extended_price-(commission_cost*qty_shipped)) AS Margin,
//Gives 75 % Margin if SalesRep ID 3449
(IF(match(salesrep_id, 3449),(extended_price-(commission_cost*qty_shipped)))*.75) AS Cowan75Precent_Margin,
//Gives 25 % Margin if SalesRep ID 3449
(IF(match(salesrep_id, 3449),(extended_price-(commission_cost*qty_shipped)))*.25) AS House25Precent_Margin,
//Old Way to Taking Away Margin from Total Margin.  See NonSpilt_Margin
(IF(match(salesrep_id, 3449),(extended_price-(commission_cost*qty_shipped)))) AS Cowan75House25SplitTotal_Margin,
//Gives 50 % Margin if SalesRep ID 3293
(IF(match(salesrep_id, 3293),(extended_price-(commission_cost*qty_shipped)))*.50) AS HawkinsonHouse50_Margin,
/*Margin Expressions Used in Margin Pre SalesRep Pivot Table*/
//Old Way of caculating Margin 
Margin: 
sum(Margin)-sum(Spilt5050Account_Margin)/2+sum(com_co)
-sum(Cowan75House25SplitTotal_Margin)
+sum(IF(SaleRep='Cowan',Cowan75Precent_Margin))
+sum(IF(SaleRep='House',House25Precent_Margin))
//How I would like my 'new' Margin to be caculated 
Margin Adj:
sum(NonSpilt_Margin)+sum(com_co)
+sum(IF(SaleRep='Hawkinson',HawkinsonHouse50_Margin))
+sum(IF(SaleRep='House',HawkinsonHouse50_Margin))
+sum(IF(SaleRep='Cowan',Cowan75Precent_Margin))
+sum(IF(SaleRep='House',House25Precent_Margin))
//No Allocations Just straight Margin Totals
Margin No Changes:
sum(Margin)+sum(com_co)

The correct Margin total should be $1,569,115

The correct Margin sum for Cowan75/House25 should be $29,931

The correct Margin Split for Cowan for Cowan75/House25 should be $22,448

The correct Margin Split for House for Cowan75/House25 should be $7,482

The correct Margin sum for Hawkinson/House should be $47,068

The correct Margin Split for Hawkinson for Hawkinson/House should be $23,534

The correct Margin Split for House for Hawkinson/House should be $23,534

See equations for on the far right for break down of the values in screenshot. I am using the same logic on the un-even splits as I am on the even splits. I do not understand why it is generating a different values? ($1,106,099? number) Any one have any ideas? Any other ideas on how to do this? Any one else run into this? Why would the even splits be handled differently by QV than the un-even splits?

Re: Allocate Sums to other Values

Wed, 18 Apr 2012 20:39:59 GMT

I was playing around this and discovered using Set Analysis returns the correct value.

sum({$<SaleRep = {"Hawkinson"}>} HawkinsonHouse50_Margin)

However the sum of this now includes itself in all totals. Does this help anyone? Why would this work?

Re: Allocate Sums to other Values

Thu, 19 Apr 2012 13:11:06 GMT

Using SalesRep name this worked out however name was also my sorting demension so I gave each SalesRep a unique ID. Then I used this ID in my set analysis expression.

SalesRepMaster:
LOAD * inline [
          last_name, SaleRep, SaleRepCustom_ID
          Cowan75/House25, Cowan, 1
          Jones/House, Jones, 2
          Bredehoft/House, Bredehoft, 3
          Cowan/Hawkinson, Cowan, 4
          Finn/House, Finn, 5
          Hawkinson/House, Hawkinson, 6
     ....
];
/*Margin in Pivot Table*/
+sum({$<SaleRepCustom_ID = {4} >} CowanHawkinson50_Margin) 
+sum({$<SaleRepCustom_ID = {28} >} CowanHawkinson50_Margin)

Hope this can help someone else who needs to do allocations 'manually' for margin/sales distribution.

topic Re: Allocate Sums to other Values in QlikView

Allocate Sums to other Values

Re: Allocate Sums to other Values

Re: Allocate Sums to other Values