Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

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

Re: Variance Calculation in Script

Can you provide a sample with expected output?

Not applicable

Re: Variance Calculation in Script

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

balrajahlawat
Esteemed Contributor

Re: Variance Calculation in Script

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
Honored Contributor III

Re: Variance Calculation in Script

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

Re: Variance Calculation in Script

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

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

Re: Variance Calculation in Script

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

Community Browser