I have a filter function in combination with the CHOOSECOLS, SORT and IFERROR function. I use this to draw a dynamic graph in Excel. At first it seems to work fine. However when I select values that have no data (resulting in "-- No Result --" on screen) the graph breaks.
Because after I have the message on screen ("-- No Result --") and I select other values that do have a result, the graph stays empty. How can I solve this?
=IFERROR(CHOOSECOLS(SORT(FILTER(tbDataManager;(tbDataManager[ICT-service]=Application)*(tbDataManager[Rapport period]>=StartPeriod)*(tbDataManager[Rapport period]<=EndPeriod);"");2;1);1;2;3;4;7;6;8);"-- No Result--")
The input is the ICT-Service and a start- and enddate resulting in a filtered table. I select the columns of this table I want to show in a graph. In this case a line graph.
As said, when I select valid data after I have had the No Result message, the graph stays empty where it should show the graph again, because this time I have data.
Steps:
- Select values that result in data, graphs shows fine (correct).
- Select values that result in No Data, graph shows No Data (correct)
- Select values that result in data, graph stays empty (Not correct it should show the lines because there is data to show)
UPDATE (20250820 10:30)
I notice that when there is only one line to show, the graph didn't show anything and was broken. I found that this was cause by the type of graph (Line graph). Now I have a Line graph with markers. In this case when only one records results from the filter function, the graph keeps working and when I select an other value that results in multiple records, the graph still works fine.
The only time it is still going wrong is when there is nothing to show (using NA() or "" doesn't matter). In this case when I select an other value that does have a result after I pervious had an empty result, the graph won't show anything anymore. It seems like the series and categories have been reset and stay that way after an empty value was the result of the filter function.
UPDATE (20250820 13:30):
I did some more investiogation and I found that part of the problem was the inconstency of the source. The Periode column is in a format of yyyy-q but some of the row didn't have the q defined. So in my case I replace all the empty Q's with 1 in order to have a valid value for all the rows.
Based on Solar Mike's suggestion, also replace the [empty part] of the FILTER function with NA and removed the IFERROR part of the function.
I also found that it is a problem when the data used to draw the graph cannot have a column in between that is not used to draw the graph like
The first two columns I use for the series and the last two for the line drawing. So column 3 and 4 are not used here. This also caused also a problem, because when selecting a value that results in a NA value and after this selecting a valid value, the graph doesn't show anymore it stays empty no matter what I select. So I had to alter the function and leave these columns out so that the series values and data values are next to each other. Don't know is this is normal behaviour.
The only problem remaining now is when a value is select that has no result (NA), resulting in an empty graph and after I select a valid value again the Legend Entries and Horizontal labels are switched. So I got a different graph showing.
No result?