Hey Guys,
We are trying to create a chart for Price Volume Mix. So far we have been failing in aggregating the sums correctly. I am sharing our Price calculation formulae for where are failing to get the correct calculation along with the Excel formulae which we are trying to replicate. Any help would be appreciated.:
Problem Description:
Price Volume mix margin bridge is a method to connect the components like price, volume, cost and mix between any two periods. being done at dimension called Item Number.
This price formula in Excel which we are trying to replicate in Qliksense for every item and then aggregation or sum :
IF(OR(Cems="E",Cems="C",),IF(OR("Quantity New Period"=0,"Quantity Old Period"=0),0,("unit Price New Period"-"Unit Price Old Period")*"Quantity New Period"),0)
Formulae in Qlik which not giving us the correct aggregation:
sum(aggr
(
if
(
num(
sum({<Month={'$(vMV_Variable4)'},Year={'$(vMV_Variable2)'},[Items.CEMS Code]={'C','E'},[Intercompany Flag]={'Third Party'},DateType={'Invoice Date'},
ToCurYM_Spot={$(='USD|'&year(addmonths(today(),-1))&num(month(addmonths(today(),-1)),'00'))}>}[InvoiceLines.Quantity Shipped])
)=num(0)
or
num(
sum({<Month={'$(vMV_Variable3)'},Year={'$(vMV_Variable1)'},[Items.CEMS Code]={'C','E'},[Intercompany Flag]={'Third Party'},DateType={'Invoice Date'},
ToCurYM_Spot={$(='USD|'&year(addmonths(today(),-1))&num(month(addmonths(today(),-1)),'00'))}>}[InvoiceLines.Quantity Shipped])
)=num(0)
,
0.00
,
(
(
(
sum({<Month={'$(vMV_Variable4)'},Year={'$(vMV_Variable2)'},[Items.CEMS Code]={'C','E'},[Intercompany Flag]={'Third Party'},DateType={'Invoice Date'},
ToCurYM_Spot={$(='USD|'&year(addmonths(today(),-1))&num(month(addmonths(today(),-1)),'00'))}>}[InvoiceLines.Net Invoice Amount]*[Spot_Rate])
)
/
(
sum({<Month={'$(vMV_Variable4)'},Year={'$(vMV_Variable2)'},[Items.CEMS Code]={'C','E'},[Intercompany Flag]={'Third Party'},DateType={'Invoice Date'},
ToCurYM_Spot={$(='USD|'&year(addmonths(today(),-1))&num(month(addmonths(today(),-1)),'00'))}>}[InvoiceLines.Quantity Shipped])
)
-
(
(
sum({<Month={'$(vMV_Variable3)'},Year={'$(vMV_Variable1)'},[Items.CEMS Code]={'C','E'},[Intercompany Flag]={'Third Party'},DateType={'Invoice Date'},
ToCurYM_Spot={$(='USD|'&year(addmonths(today(),-1))&num(month(addmonths(today(),-1)),'00'))}>}[InvoiceLines.Net Invoice Amount]*[Spot_Rate])
)
/
(
sum({<Month={'$(vMV_Variable3)'},Year={'$(vMV_Variable1)'},[Items.CEMS Code]={'C','E'},[Intercompany Flag]={'Third Party'},DateType={'Invoice Date'},
ToCurYM_Spot={$(='USD|'&year(addmonths(today(),-1))&num(month(addmonths(today(),-1)),'00'))}>}[InvoiceLines.Quantity Shipped])))
)
*
(
sum({<Month={'$(vMV_Variable4)'},Year={'$(vMV_Variable2)'},[Items.CEMS Code]={'C','E'},[Intercompany Flag]={'Third Party'},DateType={'Invoice Date'},
ToCurYM_Spot={$(='USD|'&year(addmonths(today(),-1))&num(month(addmonths(today(),-1)),'00'))}>}
[InvoiceLines.Quantity Shipped])
)
)
),
[Items.Item Number]
)
)
Trying to find out what am i doing wrong here.
Any help would be appreciated.
Thanks
