Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
how can i insert 1 or more empty "divider" collumn in a pivot table ?
1. excel file, which determines which data should be shown in the pivot table (dimensions)
Nr. | ID | location |
---|---|---|
1 | 11 | here |
2 | space | here |
3 | 13 | here |
4 | 24 | there |
5 | 25 | there |
2. Pivot table, which displays
NR | 1 | 3 | 4 | 5 | ||||
Name | Alice | Charly | Diego | Emil | ||||
Location | A | B | A | B | A | B | A | B |
here | 100 | 200 | 100 | 200 | 100 | 200 | 0 | 0 |
but ist should display space too.
NR | 1 | 2 | 3 | 4 | 5 | |||||
Name | Alice | SPACE | Charly | Diego | Emil | |||||
Location | A | B | A | B | A | B | A | B | A | B |
here | 100 | 200 | 100 | 200 | 100 | 200 | 0 | 0 |
of course, there are no values or dates for the name "space" because its a placeholder.
hide empty cells is disabled.
Any suggestions ?
That's probably because SPACE is represented as null value, and null values in dimensions don't show if they have no expression values associated.
You should read it in as something other than a null using NullValue, or, for a quick and dirty solution, just stick a hard space there in Excel 😉 Of course, if it's not null, it would show everywhere you make a selection on ID (listboxes etc.) but maybe it's not a problem in your case. Also, if it has no dates etc. associated, it would disappear every time you make a selection on date, and so on.
Thanks for the reply Kuba_Michalik,
i thought about giving space the ID 0000, but unfortunately, this was already taken.
of course, since the "space" ID has no data, its not listed where this data is required for display.
i thought that there is some workaround for that - some function that provides a placeholder.
selections in listboxes are a problem too - the date for the displayed data has to be choosen by the user.
i seems that i have to create dummy data for the placeholder collumn. at least i can insert it as often as i want then.
so dummy data til 2050 it is then. i thought that there must be something like a dummydata(0,'#,##') function.
yes, hide empty cells / suppress zero-values is disabled.
At the Moment its necessary to use dummy values for the "placeholder collumn". But it can be used as many times as necessary.
Yes, hide empty cells / suppress zero-values is disabled.
At the moment its necessary to use dummy values for the placeholder unit.
if one unit has no data in the selected timeframe (february = ok, march = missing), instead of an empty collumn with the unit name, its not shown at all.
so the user sometimes doent even know that the data is missing
I can't seem to replicate your issue. Can you upload your specific one?
See attached.