Conditional Calculation and formatting

Ellina Mnikh
Ellina Mnikh Customer
10 Comments Second Anniversary
edited September 2020 in Platform
Hi all,

I have a dataview with KPI as row and month as column.  I created two different cubes with the same structure: Cube A with actuals and Cube B with the plan data.
I'm looking for the possibility in board to automatically switch the cube values when the new month begins. Means as soon a new month begins (f.e. Sep 20) the data view should use the values from the Cube A with actuals. All the future month (f.e. Okt 20) should still be filled from the Cube B with plan data.
Ideally, it can be seen from the formatting whether the month is filled from plan or actuals.
Jan 20 Feb 20 Mrz 20 Apr 20 Mai 20 Jun 20 Jul 20 Aug 20 Sep 20 Okt 20 Nov 20 Dez 20
EBITDA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeB cubeB cubeB
Depreciation cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeB cubeB cubeB
Interest cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeB cubeB cubeB
EBT cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeA cubeB cubeB cubeB
Thanks in advance!

Regards,

Lina

------------------------------
Ellina Mnikh
Controller
------------------------------
Tagged:

Answers

  • Brendan Broughton
    Brendan Broughton Active Partner
    Third Anniversary 100 Comments 100 Up Votes 25 Likes
    edited September 2020
    Hi @Ellina Mnikh,

    In simple terms the solution is to add an algorithm, show the algorithm and hide everything else (ie. cube A and Cube B).

    More detailed, I'd bring in a second Cube A (block C) and total it by Month (I do this to 'remove' the 'Account' type dimension from the algorithm - then the algorithm will just work on the Month).  The alternate to this is if there is another cube which you can use to tell you if there are Actuals for the month or not.  If you have any other selects on the screen (eg. Divisions, entity, cost centre) then it's up to you if you want to also total by these in block C.

    If the algorithm is block D, then I would put the algorithm as D=if(C<>0,A,B).  So if there are actuals (ie. C<>0), show A, if there are none (ie C=0), Show B.  Then display this block.
    You could then use block C for your alerts on Block D...if C=0 'format as Plan', if C<>0 format as actual

    hopefully that gives a few things to work with to get there.

    ------------------------------
    Brendan
    bbroughton@bi5.com.au
    Australia
    ------------------------------
    -------------------------------------------
  • Ellina Mnikh
    Ellina Mnikh Customer
    10 Comments Second Anniversary
    edited September 2020
    Hi Brendan,

    thank you!

    Regards,

    Lina

    ------------------------------
    Ellina Mnikh
    Controller
    ------------------------------
    -------------------------------------------

Categories