Set analysis - show only the current year doesnt work

[Anonymous]

Good day,

Could you, please, help me with my formula? The simplified version of the formula: (# of containers / working hours )*24 and is supposed to show the figure for this year only. But somehow it doesn't work. I assume because I need to add {1} somewhere else in the formula but cannot figure out where.

I added the condition, if # of working hrs i < 0, change to 0. I am also taking only those entries that are not Null, hence all these parts about 'len'. 

round(count({1<type={'move'}, Year = {"$(=Max({1}Year))"},vessel_crane-={"=len(trim(vessel_crane))=0"},visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} container) /sum(distinct(aggr({1< Year = {"$(=Max({1}Year))"},vessel_crane-={"=len(trim(vessel_crane))=0"},visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} (if(((t_max - t_min)*24) <0,0,(t_max - t_min)*24)), vessel_crane,visit))))

[RadovanOresky]

Hi,

if by "this" year you mean the current year, than I would suggest you use the today() function:

{< Year = {$(=year(today()))} >}

If you want the value to NOT be changed by any other selection, than add "1" at the beginning of the SET:

{1< Year = {$(=year(today()))} >}

[Anonymous]

Thank you for the reply!

I used:

round(count({1<type={'move'}, Year = {$(=year(today()))}, vessel_crane-={"=len(trim(vessel_crane))=0"}, visit-={"=len(trim(visit))=0"},
CRANE-={"=len(trim(CRANE))=0"}>} container)
/sum(distinct(aggr({1< Year = {$(=year(today()))},vessel_crane-={"=len(trim(vessel_crane))=0"},visit-={"=len(trim(visit))=0"},
CRANE-={"=len(trim(CRANE))=0"}>}
(if(((t_max - t_min)*24) <0,0,(t_max - t_min)*24)), vessel_crane,visit))))

The result is sitll the same i.e. it picks up ALL years.

[RadovanOresky · Accepted solution]

Hi,

It's a bit hard without the data to check it, but I think that you need to put the SET also in the Sum() function and not just the Aggr. The Aggr will create a virtual background table, but the Sum will still calculate through the whole current dataset.

So the demominator should be:

sum(distinct {1< Year = {$(=year(today()))}, vessel_crane-={"=len(trim(vessel_crane))=0"}, visit-={"=len(trim(visit))=0"}, CRANE-={"=len(trim(CRANE))=0"}>} Aggr(...

Also, it is advised to always use aggregation function, even in Aggr(). Right now you have no function used for (t_max - t_min), so by default Qlik will use Only() function. Not saying that it isn't correct for what you need, just you know 🙂 

[Anonymous]

1. Thank you! It worked. This is the final version:

count({1<[TimeStamp.Calendar.Year] = {"$(=year(today()))"},type={'move'},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} container)
/sum(distinct {1<[TimeStamp.Calendar.Year] = {"$(=year(today()))"},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>}aggr((if(((t_max - t_min)*24) <0,0,(t_max - t_min)*24)), vessel_crane))

2.  I guess I am an opportunist, but if I am to adapt this formula for the graph i.e. to show the trendline MoM, using the same formula, how do I fix the graph to be static i.e. when a date range is selected, the graph will still show monthly performance for 2018?

3. Radoresky, where did you learn SET? I am losing it, totally. Providing that almost everywhere I need to use set analysis...

[RadovanOresky]

1. Glad to have hepled 🙂

2. Since you are using "1" in front of all sets I think that it should be fixed and should not react to any selections. Maybe drop the same SET inside of the Aggr function as well and that will fix the created virtual table even more.

But be careful with the "distinct" statement in the Sum() function. Might be that it works alright with your data, but I usually try to avoid that.

3. Well, it takes a lot of "try-error" attempts. And I really learned a lot from HIC's (Henric Cronstrom) blogs. Search for "primer on set analysis" or "hic set analysis" and it should give you a lot of blogs on this topic.

[Anonymous]

Thank you for the advice! Trial and error...It is not an easy path with Qlik.

About the formula, did you mean to wrap up 

count({1<[TimeStamp.Calendar.Year] = {"$(=year(today()))"},type={'move'},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} container)
/aggr(sum(distinct {1<[TimeStamp.Calendar.Year] = {"$(=year(today()))"},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>}aggr((if(((t_max - t_min)*24) <0,0,(t_max - t_min)*24)), vessel_crane),
vessel_crane)? No, it doesn't work.

[RadovanOresky]

Meant it like this:

count( {1<[TimeStamp.Calendar.Year] = {"$(=year(today()))"},type={'move'},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} container)
/ sum(distinct {1<[TimeStamp.Calendar.Year] = {"$(=year(today()))"},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} aggr( {1<[TimeStamp.Calendar.Year] = {"$(=year(today()))"},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} (if(((t_max - t_min)*24) <0,0,(t_max - t_min)*24)), vessel_crane))

[Anonymous]

Nope, it changed when the date selection was made. I guess I should create another thread.