Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you provide a sample with expected output?
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
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!
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.
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.
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;
Wow thanks a lot Sunny! I'm satisfied now really appreciated the help. Thanks to everyone else for sharing and commenting. Fifi