Incorrect Algorithm Total on Dataview

Dylan Fitzmaurice
Dylan Fitzmaurice Active Partner
Second Anniversary First Comment
edited April 2020 in Platform

Hey Guys,

Just looking for some insight on how to solve this issue that i am having.

 

Requirements:
Has to show full Fiscal year split by months

Has to have different heading for the 'actuals' months and the 'forecast' months.

Actuals are locked and are only used for reference when deciding what your forecast months will be.

Has to have flattened view

Needs to be able to see what the total Fiscal year value (Actual months + Forecast Months) and this is where the problem lies.

 

What I've Tried:

Detail by Fiscal Year (Block J) and comes up with value shown. When I detail block j by month and add row totals it adds up correctly, but i only want it to display a single column with the sum value.

Removed Detail by month on all blocks and added month to the axis.

 

 

The Dataview:

image

The Block:

 

image

 

Axis:

image

 

 

A) Comment Cube by Fiscal Year, Company, Department, Region, Acc, Sub-Acc, Forecast Version

 Detailed by Fiscal Year

 

B) Forecast(Cube) by Month, Company, Department,Region,Acc,Sub-Acc,Forecast Version

Hidden

Enable Data Entry

This is the cube I want the forecast data entered to go in. 

C) Rev Filter by Fiscal Year, Company, Department, Region, Acc,Sub-Acc

Just a Revenue Filter on the Dataview, Filter >0.1

 Hidden

D) Submission Flag by  Fiscal Year, Company, Department, Region, Acc, Sub-Acc, Forecast Version

* Flag that Locks B & I

* Detail by Month

Hidden

E) Actuals Month Flag  By Month

Detail by Month 

Hidden

F) Actuals (Cube)  Month, Company, Department, Region, Acc, Sub-Acc

Hidden

Detail by Month

 

G) Algorithm, E*F 

Detail by Month

H) Forecast Months Flag by Month

Hidden 

Detail by Month

 

I) Forecast, B*H

Detail by Month

Has Reverse Algorithm to push data into block B

J) Total (Forecast + Actuals), G + I 

 

Thanks in Advance,

 

Dylan Fitzmaurice

Tagged:

Answers

  • Alexander Kappes
    Alexander Kappes Employee
    Fourth Anniversary 250 Likes 100 Comments 5 Answers

    Dear Dylan Fitzmaurice,

     

    thanks for your request.

     

    For solving your request I see two solutions.

     

    1. Disable the row totals for forecast and actuals algorithm and insert another one with: rt(g) + rt(i)

    2. Just use one algorithm for Actuals and Forecast instead of 2, this can look like: if(e=1;i,g)

     

    Hope it helps.

     

    regards

     

    Alexander Kappes

  • Dylan Fitzmaurice
    Dylan Fitzmaurice Active Partner
    Second Anniversary First Comment

    Thanks Alexander Kappes,


    Unfortunately i tried both solutions and they did not work. I'm still getting wack values.

     

    Regards,

     

    Dylan Fitzmaurice

  • Alexander Kappes
    Alexander Kappes Employee
    Fourth Anniversary 250 Likes 100 Comments 5 Answers

    Dear Dylan Fitzmaurice,

     

    did you try to switch the total of your algorithms? Perhaps this may help.

     

    Otherwise it will make sense asking your consulting partner for a solution fitting to your demand.

     

    Another idea, which can perhaps work will be disabling the row total for both of your blocks (Actuals and Forecast).

     

    Inserting another block and calculating the total with nexel (single rule).

     

    perhaps it helps.

     

    regards

     

    Alexander Kappes

Categories