Apliqo custom formulas with moving columns?

Apliqo UX
1

Hi guys,

I’m wondering if anyone has found a work-around for Apliqo custom-formula-columns when a user expands/collapses the referenced columns.

SHORT EXPLANATION OF PROBLEM:
I have inserted a custom column in my report containing a simple formula. If the user expands or collapses columns, the formula breaks. Is there a way to resolve?

FULL EXPLANATION OF PROBLEM:
I have a General Ledger report. I’ll simplify it for this example.

Let’s say I have a measures dimension with the following elements:
- N: Item Count
- C: Gross Profit

    • C: Total Revenue*
    • N: Revenue Line 1*
    • N: Revenue Line 2*
    • C: Total Cost*
    • N: Cost Line 1*
    • N: Cost Line 2*

My Apliqo report opens with the following columns displayed on columns:
Col A) Inserted column with formula: D1/B1
Col B) Item Count
Col C) Gross Profit
Col D) Total Revenue
Col E) Total Cost

When the user opens the report, this works great. However, the user also needs the ability to expand and collapse consolidations. When the user does this, the columns move and the formula in column A either displays errors or an incorrect calculation. Sometimes the user expands columns again and they are now in totally different spots with no way to get the referenced columns back in the locations the formula expects them in.

My first thought around this is to bring my Total Revenue and Item Count in twice, with the first occurrences being the first and second columns, and hiding them. Then my formula can reference columns 1 and 2 which should hopefully never move. HOWEVER… as soon as I do this, Apliqo seems unwilling to ever display them again if the user expands/collapses their parent. It seems to think they are already in the columns and hence, with the exception of the very first report open, it will NOT allow a user to expand a parent to see those columns ever again.

Is there a workaround for custom formulas on columns that a user can potentially move that won’t break?

Thanks in advance for any advice!!

2

Also CFO has gone to export the report as Excel and the custom column displays as an error and gets stripped out.

Edit: From researching, I see export issue was a known error, apparently fixed in June 2022 FP2 release. Yet more reason for us to upgrade our Apliqo version!

The custom formulas with moving column problem remains though. Not sure if an upgrade will help that one.

3

No solution for formulas breaking when expanding columns if the formulas are based on column A, B, C type reference (other than making sure that referenced columns are fixed to the left of any possible expansion. Even maybe duplicating the column and hiding it.)

However, yes to fix the issue of custom inserted columns not being exported the solution is to upgrade.

4

Thanks.

I already attempted to duplicate the columns and hide them on the left (see my notes in opening post).

Problem with that is that it then breaks the expand-collapse behavior of any related elements. So say I have a rollup like this:

Parent X
- Child A
- Child B

And Child A is one of my first duplicated hidden columns that I am bringing in especially for my calculated column… I can write my initial MDX to display columns like this:

  • A ← hidden
  • fx ← visible and calculated
  • X ← visible
  • A ← visible
  • B ← visible

The above will all work fine when the report first renders. If the user then collapses X, it will still work fine and the visible A and B will disapear from the view. However, if the user then tries to expand X again… X will not have the ability to expand.

Hence this is not a valid work-around either sadly.

If I could write my own created MDX “With Member” I could solve it. But not sure if that’s possible. Otherwise… just have to have an unhappy CFO.

5

I can’t really understand why X wouldn’t be able to be expanded after collapsing. That doesn’t really make sense.

Are all 5 columns in the same widget? You could try doing the hidden the calculated column in one widget and the other 3 in another widget then foining the widgets.

6

I don’t know why. Just the MDX seems to detect I already have a measure in a hidden column and does not want to allow me to expand a parent that has a child already hidden elsewhere in columns.

The two-joined-widgets thing sounds like a good potential workaround. I’ll look into that. (Not sure if I need to upgrade from 2021 FP7 for that or not, but we will be upgrading soon either way).

Thanks.

7

Yes you would need to upgrade to get connected widgets. But it will be well worth it to upgrade to the latest (2023 May) release as there are a huge number of new features. Note that this jump will also require change in license file.

8

Thanks!

Yeah, I’ve created new “sub-views” before via duplicating an existing view in the App Management screen back next to the original view and then configuring.

However, only ever been able to do that on “View” reports. When I try doing the same process on a view in a “Dashboard” report, the App Management screen only ever duplicates as “New Widget”, which makes it a separate floating entity on the page, which is definitely NOT what I need for the work-around.

I’m guessing that’s just the way it was back in 2021 FP7, as you have alluded to. Hopefully, with the upgrade in a couple of weeks, this will all be much easier to solve.