Closed Strange behaviour on sequence function

[Warren Meads]

I am calculating a running YTD total across 12 Months by picking up the last months calculated YTD value and adding a normal Hierarchy total. This I believe is not an out of the ordinary calculation. Here is the function restricted to "Total Income YTD" SEQUENCE("Periods", "Default", -1, ["Total Income YTD"]) + ["Total Income"]
Total income is a parent in the same dimension as Total Income YTD

I have tried both restriction on N levels and no restrictions both give the same result.

Where there are no values in a series of months the calculation is not working. It works correctly where there are balances in each month. In one instance one month was populated on its own and The YTD went for three months and then stopped. A subsquent value 3 months later gave that months YTD value but did not work for subsquent months.

In one instance I populated a value on one of the empty months and the YTD value calculated. I then removed the value but the YTD calculations continued correctly.

I have checked the memory utilistion on the saving of the rule from the logs and memory is not being exceeded.

Here it is working on a Profit center that is fully populated.


Here it is not working

 

[Ben Hill]

Hi Warren,

When using the sequence function there are a couple of things to check -

• It is best to use sequence with a hierarchy which is a flat list of elements (Can be C or N Elements) - We typically create a hierarchy called Month List for this purpose.
• The formula needs to be restricted to the same hierarchy used in the SEQUENCE function, without this it may not feed which would cause issues like you are seeing.

Hopefully that helps you get to the bottom of it. Let me know how you get on.

 

[Warren Meads]

Hi Warren,

When using the sequence function there are a couple of things to check -

• It is best to use sequence with a hierarchy which is a flat list of elements (Can be C or N Elements) - We typically create a hierarchy called Month List for this purpose.
• The formula needs to be restricted to the same hierarchy used in the SEQUENCE function, without this it may not feed which would cause issues like you are seeing.

Hopefully that helps you get to the bottom of it. Let me know how you get on.

The formula is already restricted to the Same hierarchy used in the SEQUENCE function

The Hierarchy being used only has one parent and 12 N Level elements under it. The first position is calculating correctly even though there is only the C element before it. Initally I thought it might pick up the C element and had an if statement on the first period but tried it without and it worked

@Ben Hill I have sent you a private message on other issues and those issues may also be impacting on this ie there are erroneous feeders in the cube blocking this calculation where there are no apparent values.

 

[Warren Meads]

Thanks @Ben Hill who's suggestion on making sure there is a completely flat hierarchy has solved this issue so have closed the thread

Warren