Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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!

23 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

q5.png

Anonymous
Not applicable
Author

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.