Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gerrycastellino
Contributor III

Need help on a calc

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



Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Need help on a calc

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

2 Replies
Employee
Employee

Re: Need help on a calc

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

gerrycastellino
Contributor III

Re: Need help on a calc

Nice,  the simple things trip us up sometimes .

Thankx !

Community Browser