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

# Aggregate to be used in the chart

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_C EST_DPTR_D VSL_BERTH_D CNTR_NUM 1 2018-10-22 22:12:00 2018-10-22 15:00:00 1593364 1 2018-10-22 22:12:00 2018-10-22 15:00:00 1635264 1 2018-10-22 22:12:00 2018-10-22 15:00:00 1762778 1 2018-10-22 22:12:00 2018-10-22 15:00:00 6057661 1 2018-10-22 22:12:00 2018-10-22 15:00:00 APHU7202885 1 2018-10-22 22:12:00 2018-10-22 15:00:00 APZU2132290 1 2018-10-22 22:12:00 2018-10-22 15:00:00 APZU3457448 1 2018-10-22 22:12:00 2018-10-22 15:00:00 BMOU1102734 2 2018-10-22 21:12:00 2018-10-22 15:00:00 BMOU1288070 2 2018-10-22 21:12:00 2018-10-22 15:00:00 BMOU5899415 2 2018-10-22 21:12:00 2018-10-22 15:00:00 AMFU3206555 2 2018-10-22 21:12:00 2018-10-22 15:00:00 APHU6724720 2 2018-10-22 21:12:00 2018-10-22 15:00:00 APZU3190222 2 2018-10-22 21:12:00 2018-10-22 15:00:00 APZU3677870

Many thanks, as usual!

• ###### Re: Aggregate to be used in the chart

Try this (you had placed a parenthesis at the incorrect spot

Count({<[TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year]))"}>} CNTR_NUM)/Sum(Aggr(((EST_DPTR_D-VSL_BERTH_D) *24), VESSEL_VISIT_C))

• ###### Re: Aggregate to be used in the chart

Hi Sunny,

As usual, thank you, for the help. It's good but it summed up ALL containers and DIVIDED by ALL time, instead of calculating it per month. Do I need to aggregate by Departure date as I am using Departure date for the y axis?

• ###### Re: Aggregate to be used in the chart

May be departure date in your Aggr() function

Count({<[TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year]))"}>} CNTR_NUM)/Sum(Aggr(((EST_DPTR_D-VSL_BERTH_D) *24), VESSEL_VISIT_C, [Departure date]))

• ###### Re: Aggregate to be used in the chart

I guess you know what will come next - time out.

• ###### Re: Aggregate to be used in the chart

Would it be possible for you to share a sample app?

• ###### Re: Aggregate to be used in the chart

I need to set up a sample.

• ###### Re: Aggregate to be used in the chart

If you could do that, it would be great, because otherwise it is difficult to know what you have and what you want

• ###### Re: Aggregate to be used in the chart

Hi Sunny,

Once you open, you will see that I used the 'container' field instead of COUNT NUM, and TimeStamp instead of Departure Date.

• ###### Re: Aggregate to be used in the chart

I won't be able to download anything from cloud, can you just attach it directly here

• ###### Re: Aggregate to be used in the chart

Done, I have updated the initial post. In the formula I used container instead of CNT_NUM and TimeStamp instead of Departure Date.

• ###### Re: Aggregate to be used in the chart

Based on the data provided... what is the output you are hoping to see?

• ###### Re: Aggregate to be used in the chart

Sunny, thank you for the response. I calculated it in xls: # of hrs is the sum of (Timestamp - Berth ) * 24 for all vessels. # of cont is the number of containers. metric is # of hrs / # of containers. The graph is the graphical image of the metric.

• ###### Re: Aggregate to be used in the chart

Try this

Round(count({<type={'vessel'}>}container)/Sum(Aggr(((Only({\$<type={'vessel'}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month])))

• ###### Re: Aggregate to be used in the chart

Sunny, Sunny, I don't know how you do it. It worked BUT if I select a date from the calendar, the graph will react on the selection and show the value as per the selected date, although it's supposed to stay static.

I also added the condition to show just the current year:

Round(count({<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year]))"}>}container)

/

Sum(Aggr(((Only({\$<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year]))"}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month])))

Another question, your logic doesn't seem to work if I use it to show the trend line for the previous year. Is it because TimeStamp.Calendar.Month?

Round(count({<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}container)

/

Sum(Aggr(((Only({\$<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month]

)))

• ###### Re: Aggregate to be used in the chart

To keep it static... try this

Round(count({1<type={'vessel'}>}container)/Sum({1}Aggr(((Only({1<type={'vessel'}>}TimeStamp)-Only({1} VSL_BERTH_D))*24), visit, [TimeStamp.Calendar.Month])))

• ###### Re: Aggregate to be used in the chart

For previous year... may be add Year to the Aggr() dimension also

Round(count({<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}container)

/

Sum(Aggr(((Only({\$<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}TimeStamp)-VSL_BERTH_D)*24), visit, [TimeStamp.Calendar.Month], [TimeStamp.Calendar.Year]

)))

• ###### Re: Aggregate to be used in the chart

Nope, it did not work i.e. the previous year line did not even show up.

I also tried but it did not work

Round(count({1<type={'vessel'},[TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}container)

/

Sum({1}Aggr(((Only({1<type={'vessel'},[TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}TimeStamp)

-

Only({1<[TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>} VSL_BERTH_D))*24),

visit,[TimeStamp.Calendar.Month] ,[TimeStamp.Calendar.Year])))

• ###### Re: Aggregate to be used in the chart

I am not sure I understand the issue... would you be able to show via data or share a sample?

• ###### Re: Aggregate to be used in the chart

Thank you for the responses! I have updated the vessel xls attached to the original post - basically duplicated some rows from the January 2017 but changed the year to 2018.

• ###### Re: Aggregate to be used in the chart

Annd  I have it, I just converted the table to the graph. Beautiful graph! Thank you, Sunny!

Thank you, Sunny! Sunny, I know I am pain in the neck, but could you, please, take at look at Ignore date selection from calendar but not other filters?

So far I have seen on the forum that some people here are experts in data models, some, in formulas. You seem to belong to the latter.

• ###### Re: Aggregate to be used in the chart

It works perfectly! Slightly changed to

Round(count({1<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year]))"}>}container)/

Sum({1}Aggr(((Only({1<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year]))"}>}TimeStamp)-Only({1} VSL_BERTH_D))*24), visit, [TimeStamp.Calendar.Month])))

to get this year values only

Sunny, just two more questions:

1. I look at the options I tried, and there was one similar to yours, but I did not have TimeSTamp.Calendar.Month. With this part, you are aggregating by month, are not you?

2. So, when I need to fix, I need basically to 1s whatever I only can - I see that you used '1' in almost all the fields, don't I?

• ###### Re: Aggregate to be used in the chart

1) Yes, aggregating by Month and Visit, both

2) Yes... if you don't want your expression to change based on any selection... you need 1 in all places which have aggregation.... sometimes you don't have an aggregation, but in order to ignore selection you need to add one... like we did for VSL_BERTH_D.

• ###### Re: Aggregate to be used in the chart

The weird part is that if you create table and use the formula below to calculate the last year metric, it is shown  in the tbl but it's not show on the graph.

Round(count({1<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}container)

/

Sum({1}Aggr(((Only({1<type={'vessel'}, [TimeStamp.Calendar.Year] = {"\$(=Max({1}[TimeStamp.Calendar.year])-1)"}>}TimeStamp)-Only({1} VSL_BERTH_D))*24), visit, [TimeStamp.Calendar.Month])))