Conditional Calculation and formatting

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

cubeB

Depreciation

cubeA

cubeB

Interest

cubeA

cubeB

EBT

cubeA

cubeB

Thanks in advance!

Regards,

Lina

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

Find more posts tagged with

Usage

Accepted answers

All comments

Brendan Broughton

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

.

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

Hi Brendan,

thank you!

Regards,

Lina

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