Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
SchalkF
Contributor III
Contributor III

Ratio calculation not executed properly in Total part of Column

Hi Guru's

I have a situation where the calculation is not correct and I am picking it up in the Total part of my straight table.

It might be a simple fix, but I am not sure.

 

SchalkF_1-1687789790874.png

 

 

This is a breakdown of the line item to its core components, where the 1st Base Item makes up 90% of the final product, and the 2nd Base Item makes up 10%.

Item Quantity - is the original quantity sold on the line item (70 units)

Primary Qty - amount of the Base Item that goes into the final product

Ratio Calculation:
      SUM(AGGR(
      Sum(BPrimaryQty)
     ,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
     SUM(AGGR(nodistinct
     Sum(BPrimaryQty)
     ,[Document Number],[Act Line Num],[Item Code]))


Baseprice:

     Sum(distinct prim_basePrice)

TargetFinalPrice:

 (
       SUM(AGGR(
       Sum(BPrimaryQty)
       ,[Document Number],[Act Line Num],[Item Code],BItemCode))
    /
       SUM(AGGR(nodistinct
       Sum(BPrimaryQty)
       ,[Document Number],[Act Line Num],[Item Code]))
 )
*
    SUM(distinct prim_basePrice)

 

This works fine for the line item because the Recipe Ratio * Baseprice is used correctly, 90% of 28 000, and 10% of 150 000.  But for the Total calculation it is using 100% of 178 000.  I hope this makes sense.

How can I change my calculation to first execute the line item calculation and then the Total line calculation?

Thanks in advance

Schalk

Labels (1)
  • Chart

1 Solution

Accepted Solutions
SchalkF
Contributor III
Contributor III
Author

I resolved my own issue.

I required another Aggr with BItemCode, as below:

Sum(Aggr((
SUM(AGGR(
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
SUM(AGGR(nodistinct
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code]))
)
, [Document Number],[Act Line Num],[Item Code],BItemCode))

 

View solution in original post

1 Reply
SchalkF
Contributor III
Contributor III
Author

I resolved my own issue.

I required another Aggr with BItemCode, as below:

Sum(Aggr((
SUM(AGGR(
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
SUM(AGGR(nodistinct
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code]))
)
, [Document Number],[Act Line Num],[Item Code],BItemCode))