topic Re: Variance Calculation in Script in QlikView

Variance Calculation in Script

Mon, 26 Jan 2026 18:19:17 GMT

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

Re: Variance Calculation in Script

sunny_talwar — Mon, 29 Feb 2016 19:47:23 GMT

Can you provide a sample with expected output?

Re: Variance Calculation in Script

Tue, 01 Mar 2016 07:31:20 GMT

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

Re: Variance Calculation in Script

Anonymous — Tue, 01 Mar 2016 07:42:05 GMT

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!

Re: Variance Calculation in Script

Digvijay_Singh — Tue, 01 Mar 2016 07:50:34 GMT

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.

Re: Variance Calculation in Script

Tue, 01 Mar 2016 09:59:05 GMT

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.

Re: Variance Calculation in Script

sunny_talwar — Tue, 01 Mar 2016 12:09:01 GMT

Does this look like what you wanted?

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;

Re: Variance Calculation in Script

Tue, 01 Mar 2016 14:12:36 GMT

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