Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Help in calculating variance dynamically in Table/Pivot table.

Accepted answer
73
Views
8
Comments
Rank 6 - Analytics Lead

I have sprint estimates and I want calculate difference between average and each sprint total estimates.

I am getting below in OAC.

Iteration

Estimates

Product Average

Variance

241002

152

107.0

0.0

241016

162

123.0

0.0

241030

173

135.0

0.0

241113

325

134.0

0.0

241120

395

198.0

0.0

I want like this.

Iteration

Estimates

Product Average

Variance

241002

152

241.4

-89.4

241016

162

241.4

-79.4

241030

173

241.4

-68.4

241113

325

241.4

83.6

241120

395

241.4

153.6

Attached DVA file also for quick reference

Appreciated help.

Welcome!

It looks like you're new here. Sign in or register to get started.

Best Answer

Answers

  • Rank 5 - Community Champion

    Hi @Rajakumar Burra, I downloaded the data from your sample into Excel and removed duplicates by Story ID. Then I imported the data back into OAC. Please find attached the solution.

  • Rank 6 - Analytics Lead

    Thanks @Alex Rubin-Oracle for quick reply.

    Unfortunately I can’t remove the duplicates . Spreadsheet is just example and dataset is SA and it will have multiple duplicate ids.

    I need to calculate the average based on users and based on iterations and few other Columns as well.

  • Rank 5 - Community Champion

    There is no easy formula to solve the average calculation question if there's only one table like in the example. The data model needs to change: 2 datasets - one with distinct story IDs and the other with all stories, connected by the iteration and the stories. That will allow you to group and calculate averages.

  • Rank 6 - Analytics Lead

    @Rajakumar Burra

    This is how I would approach it. I usually like to break down these tricky calculations:

    1) Calculate the Numerator for the Average :
    SUM(MIN(Estimates BY Iteration)

    2) Calculator the Denominator for the Average:
    SUM(COUNT(DISTINCT 1 BY Iteration))

    3) Calculate your Product Average by dividing (1) by (2)

    4) Calculate your variance by subtracting Product Average you get in step 3 from Estimates

    I tried this on a sample dataset and it works for me

  • Rank 6 - Analytics Lead

    Thanks @Dhaval Parikh Stantec

    Please can you share the DVA file if possible. Still I am struggling with your solution.

  • Rank 6 - Analytics Lead

    Thank you very much @Dhaval Parikh Stantec .

    Your solution worked out.

  • Rank 6 - Analytics Lead

    Glad it worked out!

Welcome!

It looks like you're new here. Sign in or register to get started.