Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Variance Calculation in Script

Hello community,

I'm trying to calculate the variance in the following script:

=NUM(SUM(vCurrentDate) Amount - SUM(vPreviousDate) Amount) /  SUM(vPreviousDate) Amount

Where vCurrentDate and vPreviousDate are variables.

How would i calculate the variance in the script?

Thanks.

Fifi Nokoe

7 Replies
sunny_talwar

Can you provide a sample with expected output?

Not applicable
Author

So essentially I would want the output as field name, in order to use the class function to create variance ranges because i can't seem to use the expression below expression in the calculated expression with the class function  ie

=NUM(SUM(vCurrentDate) Amount - SUM(vPreviousDate) Amount) /  SUM(vPreviousDate) Amount as Myfield

Anonymous
Not applicable
Author

I would suggest, if you want to calculate Variance with Current Data and Previous data, Using Set analysis you can do it easily at UI...

Chekc this?

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Else share a Sample as asked by Sunny!

Digvijay_Singh

Scripting we do to model and transform the data in the data tables so that desired KPIs can be shown through charts. If you need variance calculation against specified dimensions/fields in script, you need to use sum, group by, peek/previous kind of functions in the script. Please share sample data and script to help further.

Not applicable
Author

Hello Guys,

As requested please find attached,once again I would want the variance calculated in the script to enable me create ranges using in the class function in the pivot table or it there's any other way to achieve that I'd really appreciate it.

sunny_talwar

Does this look like what you wanted?

Capture.PNG

Script:

TEMP:

CrossTable(DATE, BALANCE)

LOAD CustomerName,

    [42427],

    [42428],

    [42429]

FROM

[DATA_FILE.xlsx]

(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

FINAL_TABLE:

LOAD DATE,

  CustomerName,

  If(CustomerName = Peek('CustomerName'), (SUM(BALANCE) - SUM(Alt(Previous(BALANCE), 0)))/SUM(Alt(Previous(BALANCE), 1)), 0) AS VarianceBalance

GROUP BY DATE, CustomerName;

LOAD DATE(NUM#(DATE),'dd/MM/yyyy') AS DATE,

    BALANCE,

    CustomerName

RESIDENT TEMP

Order By CustomerName, DATE;

DROP TABLE TEMP;

Not applicable
Author

Wow thanks a lot Sunny! I'm satisfied now really appreciated the help. Thanks to everyone else for sharing and commenting. Fifi