FYTD calculation error - Calendar issue or not?
Brendan Broughton
Active Partner
Just wondering if anyone has come across this issue before? It is in a client's capsules running 10.5.1, and I've just moved it to the most recent 10.6 and it's there too..
I display a cube with a dimension 'by columns', and day by rows (or/and a single day selection on the screen). The Dimension 'by columns' is 'Phase Billable'. It has 2 members, Billable and Non-billable.
When I turn on Yearly Cumulated Value, and check 'Fiscal Year' only the 'Non-Billable' entity member correctly calculates on 30/6/2020 (the last day of the FY). The other entity member 'Billable' is incorrect, the value it shows as the cumulated for 30/6/2020 is just the value for that one day.
I tried this without Fiscal Year checked, and the same issue occurs on 31 December 2019, the last day of that calendar year.
Why does it work for one entity member and not the other?
Below shows the only 2 trees involved in the dimensions (excluding the Time tree obviously).
The cube I am displaying (C below), is dimensioned by the YELLOW entities, and there is a select on the red outlined entity in the procedure to populate the cube. The original population is via a dataflow with a mapping cube.. C = A*B
A : Day/Employee/Proj.Phase
B : Employee/FY/Division (Mapping cube, 1 or 0)
C : Day/Employee/Phase Billable/Division (Target)
Things I've tried:
The daily values in the cube are correct, the only issue is the last day of the period when doing an Yearly Cumulated Value.
Yearly Cumulation is a time function, so I don't understand why it would have anything to do with the other dimension? The fact that ONE entity member cumulates correctly suggests to me that it's not an issue with the time dimension either.
Has anyone had a similar issue before?
It's the second Yearly Cumulated issue I've found this week, and both relate to the last period in the cumulation.
------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
I display a cube with a dimension 'by columns', and day by rows (or/and a single day selection on the screen). The Dimension 'by columns' is 'Phase Billable'. It has 2 members, Billable and Non-billable.
When I turn on Yearly Cumulated Value, and check 'Fiscal Year' only the 'Non-Billable' entity member correctly calculates on 30/6/2020 (the last day of the FY). The other entity member 'Billable' is incorrect, the value it shows as the cumulated for 30/6/2020 is just the value for that one day.
I tried this without Fiscal Year checked, and the same issue occurs on 31 December 2019, the last day of that calendar year.
Why does it work for one entity member and not the other?
Below shows the only 2 trees involved in the dimensions (excluding the Time tree obviously).
The cube I am displaying (C below), is dimensioned by the YELLOW entities, and there is a select on the red outlined entity in the procedure to populate the cube. The original population is via a dataflow with a mapping cube.. C = A*B
A : Day/Employee/Proj.Phase
B : Employee/FY/Division (Mapping cube, 1 or 0)
C : Day/Employee/Phase Billable/Division (Target)
Things I've tried:
- I thought perhaps it was a relationship issue, so I made sure it was all full (bottom up) and that didn't fix it
- I then 'Fixed' relationships to fill 'top down' relationships (in a test so I could undo) and it still didn't work.
- I tried populating from this cube into a new cube with Yearly Acumulated Applied - same issue.
- Change the code's on 'Phase Billable' to see if it was an order or code issue - still shows incorrectly.
- Tried different structures of the displayed cube to see which work. They were either populated directly from the dataflow above, or as a secondary dataflow from the initial target cube:
- Day/Emp/Division/Proj.Phase - works (but too large to display easily)
- Day/Emp/Phase Billable - doesn't work - 'no div' below
- Day/Div/Phase Billable - doesn't work - 'no emp' below
- Day/Phase Billable - works but it needs division- 'no div no emp' below
- Day/Emp/Div - Populated with a select on 'BILLABLE' entity member. Gives a different result, but that is not correct either - second snip below
- Day/Division/Proj.Phase - doesn't work - 'no Emp 2' below
The daily values in the cube are correct, the only issue is the last day of the period when doing an Yearly Cumulated Value.
Yearly Cumulation is a time function, so I don't understand why it would have anything to do with the other dimension? The fact that ONE entity member cumulates correctly suggests to me that it's not an issue with the time dimension either.
Has anyone had a similar issue before?
It's the second Yearly Cumulated issue I've found this week, and both relate to the last period in the cumulation.
------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
0
Answers
-
Just something more to add. For some reason I've also been having issues with the Day selections with this same DB. The caps above were with 2 days selected, 29 & 30 Jun 2020.
What you'll see below is that these 2 dates are still showing as the only 2 selected, yet for some reason the screen is showing ALL days after clicking 'OK' in a layout edit box. If you then 'Refresh' the screen it correctly shows only the 2 days.
The select box in the screenshot shows a tick on 'Days', and that all members are selected, yet when you click through it only shows 2 selected.
So... perhaps it is a calendar issue. Anyone had an issue like this and been able to resolve it?
This customer has recently been acquired by a foreign company and now need to run 2 different Day/Month/FY structures. We are going to implement this change using Custom Time entities, so I would like to ensure the current time relationships/entities are working correctly first..
------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
-------------------------------------------0