Need help on a calc

gerrycastellino — Tue, 28 Apr 2015 19:38:29 GMT

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)';

Re: Need help on a calc

ChristofSchwarz — Tue, 28 Apr 2015 19:46:24 GMT

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 ...

Re: Need help on a calc

gerrycastellino — Tue, 28 Apr 2015 19:53:03 GMT

Nice, the simple things trip us up sometimes .

Thankx !

topic Re: Need help on a calc in QlikView

Need help on a calc

Re: Need help on a calc

Re: Need help on a calc