Change Number in Pivot (Row No.)

[siegbert]

Hi, 

i try to change the numbers in the field names. They have numbers from the beginning but not values to every field, so the beginning number must be changed for each selection. (see screenshot - red marked)

Optional: The layer above has roman numbers - it would be nice to change them also (green marked)

[marcus_sommer]

A classical string-sorting isn't possible - at least not without more or less heavy nested if-loops or maybe pick(match()) constructs.

Better would be to ensure a proper sorting already within the script. This might be done by using dual() values whereby the extract of the number is set within the numeric parameter, for example with something like:

dual(Ebene4, subfield(Ebene4, '.', 1)) as Ebene4

With it you could enable a numeric sorting for those fields

An alternatively would be to use appropriate preloads of these fields with the wished order and after loading the related facts and dimensions you drop those tables again. Now you could just use the load-order within the sorting-properties.

- Marcus

[siegbert]

Hi Marcus,

a sorting in the script could not be the right way because it should show up the numbers dynamicly. If i choose another customer (dimension) then the "1. geleistete Anzahlung" isnt the first dimension with a value anymore rather the previous one. 

Sad thing is, its highly requested from the user to sort the numbers correctly. I guess i need that heavy nested if loops.

BR

Patrick

[marcus_sommer]

I'm not sure if I understand it right but for the right sorting it should be meaningless if another customer has values for all dimension-values. A quite different challenge is to display dimension-values if there are no facts available - is this the case you may need to create those facts.

- Marcus

[siegbert]

Let say we have a dimension with 5 dimension values. If every dimension value has values its not a problem at all. We have that counting in the script. But if we have values for example dimension value 3.&5. it must be transformed to 1.&2.. If the next customer has values in dimension value 2.&4. it must be also transformed to 1.&2..

[marcus_sommer]

You mean those numbers should has no relation to the numbering logic within the P&L else just a row-indicator for the UI object and with it being depending on the chosen customer and further selections?

IMO it makes a comparing of customers or even of the same customer and multiple years more difficult if the numbers are changing. Personally I see more disadvantages as benefits and would say such an approach is rather unusual as common.

Beside this such row-logic doesn't mean that you couldn't apply the above mentioned order-logics within the script because even if the row-numbers may change they will need a sorting.

- Marcus

[siegbert]

Exactly! I tried to convince the user but its a "historical" reporting thing.

So if i do this subfield/rowno. thing its only working on the first column. If i do this logic on the following columns it doesnt work well because it doesnt start counting correctly (see screenshot).

In the script i've load a scheme from the source system and linked that to my fact table. I still dont get how to do that in the script, because there are so many possibilities like, i have 20 different dimensions which must be mentioned in that logic. Maybe i'm completly wrong about that but i'm sure that it must be work in the frontend.

[marcus_sommer]

I'm not sure if that would be possible within the object. Especially if it should be working over multiple dimensions applying rowno() and/or rank() and/or count() could be become quite complex and would probably need the use of aggr() constructs with concatenating the multiple dimensions ... and the final step might be to use subfield() to pick the appropriate content for the intended usage. Rather avoided should be logics which use heavy nested if-loops because the performance may to much slowdown with it.

Therefore we comes again to the script ... in the end the work which should be done is the same only the way is different between UI and script. Within the script the task is probably much easier. I think I would start with creating a new table with the distinct customer + period (probably the year) + all your relevant dimensions (Ebene 1-4 + Konto). Afterwards I would apply a resident load with a sorting for the customer + period and the appropriate P&L sorting and using interrecord-functions like previous() and peek() to calculate the row-information for each dimension maybe at first in extra fields and then ... it would depend if they remain separate information, merged with the dimensions itself, outsourced as a kind of dimension-table maybe as an as-of-table ... I don't know.

- Marcus 

[siegbert]

I will try. thanks