Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

Allocate Sums to other Values

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)

qv_margin_salesrep.PNG

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?

Tags (3)
1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Allocate Sums to other Values

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.

View solution in original post

2 Replies
Highlighted
Not applicable

Re: Allocate Sums to other Values

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?

Highlighted
Not applicable

Re: Allocate Sums to other Values

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.

View solution in original post