5 Replies Latest reply on Jan 29, 2014 10:35 AM by Rossella Blatt Vital

    Ignore field with measures in table calculation and in split of the table

    Rossella Blatt Vital



      I have a view composed of piecharts (% of lots) splitted by a number of dimensions (CONTRACT, CLIENT_TYPE and REGION).  (see image).



      I would like to add two columns (one between CONTRACT and CLIENT_TYPE and the other one after CLIENT_TYPE) that display the TOTAL number of lots and the split between BUY and SELL (the colors in the piecharts) for the CONTRACT and for the CONTRACT & CLIENT_TYPE. Alternatively, concatenating the information in the CONTRACT and CLIENT_TYPE fields will work just fine as well.


      For example, the first contract has 251,121 lots among all client types and regions (125,548 buy and 125,573 sell). So, next to the CONTRACT column. I would like to see a text like:


      TOT LOTS: 251,121 (BUY: 125,548, SELL:125,573)


      I also would like the number 125,548 in green and 125,573 in red (same colors as the piecharts).


      Alternatively, without adding any column, another option might be to concatenate the information to the contract name. For example, let's say that the contract is ABC, then under the contract column I would like to see:



      TOT LOTS: 251,121 (BUY: 125,548, SELL:125,573)


      Also in this case, I would like the number 125,548 in green and 125,573 in red (same colors as the piecharts).



      Furthemore, I would like to display the same information FOR EACH CLIENT TYPE. Fopr example, next to BANK (or concatenated in the field itself), I would like to see:


      TOT LOTS:   96,649 (BUY: 53,709, SELL:42940)


      I tried to create string with a calculated field that concatenates the information, but it didn't work. An additional problem that I encountered with the calculated field is that I wouldn't know how to color the numbers of buy in green and sell in red.


      Whenever I try to add a new column with the information in the view, the piecharts change their level of aggregation (see image below):





      I checked the option "ignore in table calculation", but still it is not working as I was expecting.



      I also tried to create a separate view with a simple table without the piecharts thinking that I could just overlap the three views into one dashboard and make sure that the sizes are the same. It works fine for CLIENT_TYPE, but when I remove CLIENT_TYPE from the shelf, to display the sum of lots for the contract (for all client_types), then the size of the rows is different from the size of the rows in the view with the piecharts (because we don't have anymore the split among the client_type dimension). So the table with the trades for contract will not be aligned with the other two views.


      It looks like a simple problem to me, but I really can't figure out how to achieve this. 

      Any suggestion will be highly appreciated.


      Many thanks in advance