Forum Discussion

Mustafa_A's avatar
Mustafa_A
Contributor II
4 years ago

How do I override CV cells?

I have a report that uses it's own conditions to calculate a value. Is their a way to over-ride the blank/data cell in cube view, and have it run a simple IF conditional calculation?

Most of these conditional calculation are for KPI's (basically forecasting for 2023 and on..).

One of the condition within excel I'm referencing. 

 

 

Would appreciate your suggestions. Thank you!

 

Best,

Mustafa

 

 

1 Reply

  • EdWatson's avatar
    EdWatson
    New Contributor

    Hi Mustafa,

    If you have the CV hosted in a dashboard then a simple button to run through to a Business Rule would be your best approach.  Here you can bring in your variables then run the IF function to set a different formula dependant on if you X value is greater or smaller then your Y value. Typically the sub would look like the below (note this is a very simplified version):

     

    Dim Xi as integer = api.data.getdatacell({intersection for X}).cellamount * (1 + api.data.getdatacell{intersection for I}).cellamount

    Dim Y as integer = api.data.getdatacell({intersection for Y}).cellamount

    api.data.clearcalculated(true, true, true, true, "{intersection to calculate to}")

    If Y > Xi then

    api.data.calculate([intersection to calculate to] = Y)

    Else

    api.data.calculate([intersection to calculate to] = X)

    End if

     

    Regards,

    Ed