Hello ,
I have a dataview in data entry mode. However, i would like to lock the column totals
Anybody has a solution for me.
Thanks
Okay yes if the cells are blank or you only have one value then it won't work, in that case instead of using c/c just change that to be a hardcoded 1 and it should work, as it will just act as a count of all rows instead of just the ones with values.
Re. rows versus column, I see what you mean, I hadn't even spotted that I'd left it set to Row not Column, I get the same behaviour as you - setting it to Row Totals locks the column totals, and setting it to Column Totals doesn't seem to lock anything, I can try and check later if that's a known bug.
Hi @Khaleelah,
It can be achieved using the Totals Locked by block setting in the Data Entry block configuration.
The feature allows you to lock row or column totals based on the value of another block (typically a Cube or Algorithm). Once totals are locked, the values are preserved, while users can continue redistributing the detail values without changing the overall total.
For more, please refer to the Board manual: https://help.board.com/docs/about-data-block-settings?highlight=totals%20locked
Kind regards, Hamza
Hi @Hamza Mesbahi
Thank you for your prompt reply.
Actually i already tried with an Algorithm where i set abs(1) , then on the cube locked by i selected abs(1) not equal to 0. But it did not function. Will try with a cube and see.
Will keep you posted.
@Hamza Mesbahi
Tried below
The totals remained editable despite locked is as below but unfortunately the totals remain editable
Note Spli/Splat is not enabled.
Any idea please?
Hi Khaleelah, the way I've typically done this is to have an algorithm block (b) set to equal a/a (where a is the block open for data entry), the total set to total, and then a second algorithm (c) set to if(a>1,1,0), that way block c will flag any total rows and can be used in the Cells Locked by Block setting, Totals Locked by Block is actually something slightly different - it locks the total amount and not just the cell, if you adjust the row amounts the total will remain the same and the other row amounts will be adjusted to maintain the same total.
Locking the cells
hi @Robert Barkaway
I have tried what you suggested, but e will alway be 0 cause c/c = 1, this is one thing
not working. im locking column totals by the way
Rgds
Khaleelah
No if you set the Summary setting of block d to Total rather than Calculated then it will sum up all the 1's and be more than one in the total rows. d should be set to Total and e should be set to Calculated.
your algorithms look correct there, you can see that c/c has a total of 8, and as a result the next column is correctly flagging the total row as a lock, the only question is why your editable block isn't locking if you've set the locking to use the if(d>1,1,0) block? Can you show me the setting you've used on the lock itself?
Hi @Robert Barkaway
It finally works.
The lock should be done by rowtotals and not column totals and i had to detail the algorithmns by month cause the main cube is detailed by month.
But i dont understand why is it rowtotals that we are locking and not column totals?
Thanks a lot
Ah okay, yes if you're using Detail By all blocks referenced for the lock need to match the block being locked. I'm not sure what you mean about row versus column, can you show me what's still not working?
Hi @Khaleelah, your configuration looks good. But, as you noted below, you would need to use Row Totals in the Lock value of setting for it to work. However, based on Robert's proposed solution and your feedback on it, that approach seems to be a better fit for your use case. Kind regards, Hamza
Clever solution, Rob. Great one to have in the toolbox!
Thanks @Robert Barkaway
hello @Robert Barkaway
I have noticed that the values are being split in the columns when i have applied the lock, despite split/splat is disabled.
Do you have any idea why this is happening.
HI Khaleelah, hard to say without seeing quite what's happening - are you able to take some screenshots of your configuration and what's happening?
Kindly find below the snapshots
The total is remaining fixed and the values are being adjusted
Okay, so yes the totals are locked which is what you've configured, so the totals will remain the same and any changes to cells will result in the other rows being adjusted to maintain the same total.
As for the cells being open for editing despite Split Splat being disabled, I can only think that between your screen select and the dimensionality of your layout those cells are showing the most granular level of the cube and don't therefore require Split & Splat to be directly editable?
Hi
Actually i thought the changes in the cells would not be adjusted to reflect the locked total. This is what i meant when i said Split/Splat is disabled but still values are being adjusted.
So it is a normal behaviour?
Ah I see, yes this is the expected behaviour.
Split/Splat is different, it allows the user to edit the value of a cell at a higher aggregation than the dimensionality of the cube it displays as long as there's underlying data in the cube, any changes are pushed down to the lowest cube granularity respecting the existing distribution of the data (e.g. if 30% is against the UK, 40% against US and the rest split between other countries those splits will be maintained even as the numbers are adjusted).
What you have here is a situation where ordinarily if you edit a figure in one of those rows the other rows won't be impacted, but the total will change to reflect the adjustment made, by locking the total you keep those cells open for change but you say that the total must remain the same, that means it works similarly to split/splat because any change you make to a row will cause the other rows to change to keep the total static, the change you introduce into one row will be spread over the remaining rows that are open for change, again respecting the existing distribution of data.
What was your expected outcome exactly? If you can explain what you want to happen I can try and advise on what you'd need to do.
My expected outcome is that i should not allow the user to manually modify the total. But the total recomputes itself when changes are made to the rows, in what we implemented, the total doesnt change but the rows are adjusted.
Simply put, the total is non editable field but it changes based on the row values
Ah okay, in which case you need to use the Cells Locked by Block rather than the Totals Locked by Block, I see you already have a lock in there so you'll probably need to combine the two pieces of logic into a single algorithm and use that for the cell lock.
