Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ekaponkratova
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_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!

1 Solution

Accepted Solutions
MVP
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
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))

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

q2.png

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

ekaponkratova
Contributor

Re: Aggregate to be used in the chart

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

q3.png

MVP
MVP

Re: Aggregate to be used in the chart

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

ekaponkratova
Contributor

Re: Aggregate to be used in the chart

I need to set up a sample.

MVP
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

ekaponkratova
Contributor

Re: Aggregate to be used in the chart

Hi Sunny,

I have uploaded the file at app_sample - Google Drive.

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

MVP
MVP

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

Uploading a Sample