Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
i wonder if any one can assist me. When i calculate the percentage between 2020 and 2019 nett nett. the percentage total value is incorrect. Although if i decrease my selection the total is correct.
The expression is set to calculate expression values.
i am not sure if it is an error in the formula, but as you can see in the screenshots there is a difference between qlikview and excel.
=((((sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Addons_Total_Net)
+sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Lens_Total_Net)
+sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Semi_Net)
+sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Frame_Net))
* (sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}SETTLEMENT_%)))

((sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Addons_Total_Net)
+sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Lens_Total_Net)
+sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Semi_Net)
+sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Frame_Net))
*sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}SETTLEMENT_%))
)
/
((((sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Addons_Total_Net)
+sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Lens_Total_Net)
+sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Semi_Net)
+sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}Price_Frame_Net))
*sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}SETTLEMENT_%)))
))
any help in this regard will be much appreciated.
Thank you
Kallie
Hi Marcus,
thank you for your help your formula pointed me in the right direction.
(sum(aggr(
(sum({<Year = {"$(=MaxString(Year))"},ColorFamily = {'Transitions'}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)) ,
CSTM_CD))
)
Above worked correctly.
Thank you
Kallie
By a simple sum() the totals will always work like expected but by rates it calculates the totals without regarding the dimensional level  to ensure it it's often needed to wrap the calculation within an aggr(). In your case is also the summingmethod by using operators problematic because each NULL result of a parameter means NULL for the whole calculation part which is often not intented. Therefore I suggest rather an approach like the following:
sum(aggr(
(sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net) * SETTLEMENT_%) 
sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net) * SETTLEMENT_%)) /
sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net) * SETTLEMENT_%),
Dim1, Dim2))
The rangesum() will replace nonnumeric results with 0 and because of the fact that all conditions are identically it could be applied within a single aggregation  which shortened the expression to get a better overview and probably also performance  and aggr() could ensure that the calculations is performed within the right context (Dim1 and Dim2 are just placeholder for your applied dimensions  usually those one within the chart).
 Marcus
Hi Marcus,
thank you for the response i wasn't aware you could calculate it like that will make formulas much shorter.
it gives error in expression though at below part and thus not calculating
>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net) * SETTLEMENT_%)
Thank you
Kallie
Hi Marcus,
i managed to get the formula working and calculating but it is giving me a way different value 40.606%
Just check the parts of the calculation separately. If I look again on your calculation I'm not sure if the multiplying within the sum() is really suitable to your logic. Maybe it needs to be more this one:
...
(sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net)) *
sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'},CSTM_GRP_CD = {'2'}>} SETTLEMENT_%))
...
 Marcus
Hi Marcus,
thank you for the updated info, i have edited the formula to currently exclude the cstmrgrp exclusion as i understand qlikview doesn't like the syntax and then gives error. below formula shows expression ok but calculates 0 .
sum(aggr(
((sum({<Year = {"$(=MaxString(Year))"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'}>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net)
* sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'}>}SETTLEMENT_%))) 
(sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'}>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net)
* sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'}>}SETTLEMENT_%)))) /
(sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'}>}rangesum(Price_Addons_Total_Net, Price_Lens_Total_Net, Price_Semi_Net, Price_Frame_Net)
* sum({<Year = {"$(=MaxString(Year)1)"},COATTYP = {'HVLL','HVLLBLUE','HVLLUV'}>}SETTLEMENT_%)))
,Acc#Name))
if i do the calculations with the sum and normal nett nett without percentage it calculates value correct but as soon as i do the percentage it goes haywire
any assistance will be greatly appreciated
thank you
Kallie
You may need another and/or more dimensions within the aggr() to calculate it within the right context.
 Marcus
Hi Marcus,
thank you for your help your formula pointed me in the right direction.
(sum(aggr(
(sum({<Year = {"$(=MaxString(Year))"},ColorFamily = {'Transitions'}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)) ,
CSTM_CD))
)
Above worked correctly.
Thank you
Kallie