Percentage Value incorrect

[Kreyneke]

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

[marcus_sommer]

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 summing-method 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 non-numeric 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 

[Kreyneke]

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

[Kreyneke]

Hi Marcus,

i managed to get the formula working and calculating but it is giving me a way different value  -40.606%

[marcus_sommer]

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

[Kreyneke]

Hi Marcus,

thank you for the updated info, i have edited the formula to currently exclude the cstmr-grp 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

[marcus_sommer]

You may need another and/or more dimensions within the aggr() to calculate it within the right context.

- Marcus

[Kreyneke · Accepted solution]

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