Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a calc in the UI as follows.
It looks at a sum(calc) of a specific dimension for specific period, dividing by sum(calc) all dimension for a specific period:
num((sum({$<RS={ "$(=only({[Comp2]} RS))"}>} calc1) -
sum({$<RS={ "$(=only({[Comp1]} RS))"}>} calc1))
/
(sum(TOTAL <EDATE> {$<RS={ "$(=only({[Comp2]} RS))"} >} calc1)-
sum(TOTAL <EDATE> {$<RS={ "$(=only({[Comp1]} RS))"} >} calc1)), v_Format_Percent)
I have now broken this down in variables in a ".qvs" file, but not getting the desired results.
My variables in the file are as follows - The numerator and denominator are performing the correct calcs, the division(RESULT) is
way off:
LET NUMERATOR = 'sum({$<RS={ "' &chr(36)&'(=only({[Comp1]} RS))" }>} calc1) - sum({$<RS={ "' &chr(36)&'(=only({[Comp2]} RS))" }>} calc1)';
LET DENOMINATOR ='sum(TOTAL <EDATE> {$<RS={ "' &chr(36)&'(=only({[Comp2]} RS))"} >} calc1) -
sum(TOTAL <EDATE> {$<RS={ "' &chr(36)&'(=only({[Comp1]} RS))"} >} calc1)';
LET RESULT= 'num('&chr(36)&'(NUMERATOR) / '&chr(36)&'(DENOMINATOR), v_Format_Percent)';
As far as I can see is that you have a subtraction in both, the denomerator and the numerator formula. So it is simple mathematics why it fails a-b / c-d leads to a - (b/c) - d ... whereas what you want and what you initially defined was (a-b) / (c-d). Put some extra brackets around the definitions of DENOMERATOR and NUMERATOR and it should do ...
As far as I can see is that you have a subtraction in both, the denomerator and the numerator formula. So it is simple mathematics why it fails a-b / c-d leads to a - (b/c) - d ... whereas what you want and what you initially defined was (a-b) / (c-d). Put some extra brackets around the definitions of DENOMERATOR and NUMERATOR and it should do ...
Nice, the simple things trip us up sometimes .
Thankx !