23 Replies Latest reply: Nov 8, 2018 9:30 PM by Ekaterina Ponkratova RSS

    Aggregate to be used in the chart

    Ekaterina Ponkratova

      Hi guys,

       

      I have something very simple, but I cannot get the result. Below is the screenshot of the table. I need to calculate total # of hours to be used for my time series graph to be divided by the # of containers (this year)

       

      Something like

       

      count({<[TimeStamp.Calendar.Year] = {"$(=Max({1}[TimeStamp.Calendar.year]))"}>}CNTR_NUM)

      /sum(aggr(((EST_DPTR_D-VSL_BERTH_D) *24)),VESSEL_VISIT_C )

       

      So, for the example below it should be 14 / (7.2 + 6.2) = 1.0447761194029850746268656716418.

       

      VESSEL_VISIT_CEST_DPTR_DVSL_BERTH_DCNTR_NUM
      12018-10-22 22:12:002018-10-22 15:00:001593364
      12018-10-22 22:12:002018-10-22 15:00:001635264
      12018-10-22 22:12:002018-10-22 15:00:001762778
      12018-10-22 22:12:002018-10-22 15:00:006057661
      12018-10-22 22:12:002018-10-22 15:00:00APHU7202885
      12018-10-22 22:12:002018-10-22 15:00:00APZU2132290
      12018-10-22 22:12:002018-10-22 15:00:00APZU3457448
      12018-10-22 22:12:002018-10-22 15:00:00BMOU1102734
      22018-10-22 21:12:002018-10-22 15:00:00BMOU1288070
      22018-10-22 21:12:002018-10-22 15:00:00BMOU5899415
      22018-10-22 21:12:002018-10-22 15:00:00AMFU3206555
      22018-10-22 21:12:002018-10-22 15:00:00APHU6724720
      22018-10-22 21:12:002018-10-22 15:00:00APZU3190222
      22018-10-22 21:12:002018-10-22 15:00:00APZU3677870

       

      Many thanks, as usual!