halfcoupler Posted June 6, 2017 Share Posted June 6, 2017 Hi @ all, I want to count the instances of symbols separately for each design layer. I'm just wondering whether it is possible to have subtotals of counting symbol instances per several layers in one report, or do I always have to make one report with criteria for each layer ? That would be 15 reports for 15 layers, - a bit cumbersome ... I'm shure there must be a better way. Quote Link to comment
Pat Stanford Posted June 7, 2017 Share Posted June 7, 2017 How about using a simple Count function. =COUNT((((L='Design Layer-1') & (S='Symbol-1')))) You can use the Worksheet:Insert:Criteria to build the criteria that needs to be in the inside. Quote Link to comment
halfcoupler Posted June 7, 2017 Author Share Posted June 7, 2017 (edited) I'm afraid it's a bit more complicated than just counting. In the end I want to have something like a pivot-table, where colums are the layers and rows are the symbols. The corresponding fields sum up the amount of the symbol in the layer ( see attached sample ) From a database-view it's just the problem of a n:m ( many to many ) relationship between tables: A table of layers can be connected to several symbols in the symbol-table and vice-versa. In report software of databases this is usually solved by subgroups. In the meantime I have made 16 reports for 16 layers to finish my project... Table00.pdf Edited June 7, 2017 by halfcouple Quote Link to comment
RickR Posted June 8, 2017 Share Posted June 8, 2017 I think this is very do-able. Criteria would be Type=symbol, maybe class, etc Column 1 = Layer name (sub total on that) Column 2-x would hold Pats formula and should sum up. Does that make sense? This should be very similar to totaling fixtures by position. Note: Formulas are conceptual, I haven't opened VW to verify the formatting. Quote Link to comment
halfcoupler Posted June 8, 2017 Author Share Posted June 8, 2017 (edited) maybe I'm making things more complicated than tey really are, but can't get it to work: - Im using a report with database headers - Pat's formula does not seem to work in a database header row, since 'Design Layer 1' and 'Symbol 1' are Text values, but they should automatically refer to the symbol name that is called in the row and the layer name that is called in the column. of course it works in a single cell, but I dont want to manually create a calculation formula for any symbol with any layer. Edited June 8, 2017 by halfcouple Quote Link to comment
Pat Stanford Posted June 8, 2017 Share Posted June 8, 2017 The =Count() [without criteria] in a database cell will return the number of items in the row. If you are using a SUMmerize tile, it will show the number of subrows that are SUMmarized together. I think I can write you a custom Worksheet Script that will allow you to get what you want. Would this work? Create a database with criteria to give you the symbols on all layers. Create a column that displays the symbol name. Put the layer names in cells above the database in the proper column. Put a custom script that will read the symbol name from the database row and the layer name from the labels above and use that data to calculate the number of symbols on the layer and then return that information into the database cell. If that sounds reasonable, I will see what I can come up with. Quote Link to comment
halfcoupler Posted June 8, 2017 Author Share Posted June 8, 2017 'Create a database with criteria to give you the symbols on all layers. ' DONE 'Create a column that displays the symbol name. ' DONE 'Put the layer names in cells above the database in the proper column. ' DONE 'Put a custom script that will read the symbol name from the database row and the layer name from the labels above and use that data to calculate the number of symbols on the layer and then return that information into the database cell. ' FAILED So this must be something like =SUM(((L=THIS COLUMN) & S=THIS ROW)) ????? How do I refer to certain cells, colums and rows in a formula ? Quote Link to comment
Pat Stanford Posted June 9, 2017 Share Posted June 9, 2017 Yeah, the custom script would be in Python of (more likely) Vectorscript and the formula would look something like =RunScript(SumIntersection,D,6). But the script has not been written yet. You would have to put a version of the function in every column where there was a layer above. Hopefully next week. My next 4 days are non-stop. Quote Link to comment
RickR Posted June 9, 2017 Share Posted June 9, 2017 Did you mean something like this? Sum by Layer.vwx Quote Link to comment
Pat Stanford Posted June 9, 2017 Share Posted June 9, 2017 Nice divergent thinking and it gets the data out, but what I was thinking was more like this mockup: Quote Link to comment
halfcoupler Posted June 10, 2017 Author Share Posted June 10, 2017 (edited) Hey Rick ! Yes thats it !!! So easy !!! So the trick is simply to sum up BOTH columns, Layer-collumn AND Symbol-column. Didn't see that ! But the pivot table would be of course more comfortable, since it has no repetition of the layer names. I think that automatically generating the columns for the layers will be the most complicated thing to do in this task. Edited June 10, 2017 by halfcouple Quote Link to comment
Pat Stanford Posted June 27, 2017 Share Posted June 27, 2017 I don't think we can do the pivot table thing right now. There are no Functions or formulas to get the current cell that the formula is working in, so there is no way to get the relative cell that holds the "Symbol Name" in my mockup above. I have submitted a request for a function to be able to get the cell a function/script is operating in, but I am not holding my breath. Sorry. Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.