# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Contributor

## 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!

1 Solution

Accepted Solutions
MVP

## 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])))

23 Replies
MVP

## 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))

Contributor

## 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?

MVP

## 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]))

Contributor

## Re: Aggregate to be used in the chart

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

MVP

## Re: Aggregate to be used in the chart

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

Contributor

## Re: Aggregate to be used in the chart

I need to set up a sample.

MVP

## 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

Contributor

Hi Sunny,