07-29-2024 04:47 PM
Hi,
I'm wondering if anyone else has had a similar issue when creating DynamicCalc accounts that include a non-financial account member and a financial account member? I'm trying to create a revenue per day account that will use our A#SalesTot account and our A#IscSalDays account. My formula is below:
Return api.Data.GetDataCell("Divide(A#SALESTOT, E#DRIVER:C#Local:S#Plan:A#ISCSALDAYS:F#EndBal:O#Top:I#None:U1#None:U2#Top_UD2)")
When I populate a cube view with each of these accounts separately, I get the correct data, but I can't figure out why the calculated account returns blank green cells. Since I want to use the result in a dashboard chart, I can't use a column row math expression, which is how I calculate revenue per day in our other reports.
07-29-2024 07:57 PM
Hi - there should not be any issues with dynamic calcs that include Non Financial account types. Multiplication of a Revenue/Expense/Flow and a Non Financial should work fine. Can you test if the E#DRIVER:C#Local:S#Plan:A#ISCSALDAYS:F#EndBal:O#Top:I#None:U1#None:U2#Top_UD2 part of your script is returning a value by adding a row to the Cube View and pasting in that script? If may be that that intersection is not returning data and it needs modified.
07-30-2024 10:53 AM
Hi, I have a cube view with each of those accounts and the only one that doesn't return data is the calculated account.
07-30-2024 12:20 PM
Maybe the value being returned is very small (since you are doing division). Can you right click one of the cells and click 'Cell Status' and see what the amount is? Otherwise, can you screenshot the Cube View and also screenshot the Cell POV Information of each of the accounts?
07-30-2024 03:51 PM
Hi, these are the Cell POV's.
SalesDays: Stat account
Cb#CONSOL:E#DRIVER:P#?:C#Local:S#PLAN:T#2024M7:V#Periodic:A#ISCSALDAYS:F#EndBal:O#Top:I#None:U1#None:U2#TOP_UD2:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None
SalesPerDay: Calculated Account
Cb#CONSOL:E#EMI_US:P#?:C#USD:S#ACTUALS:T#2024M7:V#Periodic:A#Sales_By_SalesDays:F#EndBal_Tot:O#Top:I#Top:U1#TOP_UD1:U2#TOP_UD2:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None
Sales: Financial Account
Cb#CONSOL:E#EMI_US:P#?:C#USD:S#ACTUALS:T#2024M7:V#Periodic:A#SALESTOT:F#EndBal_Tot:O#Top:I#Top:U1#TOP_UD1:U2#TOP_UD2:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None
07-30-2024 06:28 PM
Only thing I can think of is that the account properties aren't set correctly. Is both AccountType and FormulaType set to DynamicCalc?
07-30-2024 06:45 PM
Hi, Yes It's so weird. I've triple checked it and I'm totally stuck. I can create it as a calculation in a cube view using the CVR calculation, but I want to put it in a dashboard. Thank you for your efforts!
07-31-2024 09:47 AM
The next thing I would try is to log both parts of the calc and see if you're getting data. Then you can at least narrow it down to which part is the issue.
Return api.Data.GetDataCell("Divide(A#SALESTOT, E#DRIVER:C#Local:S#Plan:A#ISCSALDAYS:F#EndBal:O#Top:I#None:U1#None:U2#Top_UD2)")
Dim salesTot As String = api.Data.GetDataCell("A#SALESTOT").CellAmountAsText
Dim ISCSALDAYS As String = api.Data.GetDataCell("E#DRIVER:C#Local:S#Plan:A#ISCSALDAYS:F#EndBal:O#Top:I#None:U1#None:U2#Top_UD2").CellAmountAsText
api.logmessage($"Sales Tot Amount = {salesTot}
api.logmessage($"Sales Tot Amount = {ISCSALDAYS}
From there you can tweak some of the members in the script until you get data.
08-01-2024 01:39 PM
Hi,
I copied in your formula and added the account to a cube view. I looked at the cell status. Is that what you meant by logging it to see if it returns data?
08-05-2024 05:54 AM
Hi Julia,
In Jon's formula the "api.logmessage" parts will write the values to the Error Log (under the System tab) - so after you open the cube view you can go to the error log and see what value is being recorded.
Once you've resolved the issues you will need to make sure you remove the log message part of the code so you don't end up with lots of unnecessary entries.
Regards