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

Set analysis - show only the current year doesnt work

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

1 Solution

Accepted Solutions
RadovanOresky
Partner Ambassador
Partner Ambassador

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 🙂 

View solution in original post

8 Replies
RadovanOresky
Partner Ambassador
Partner Ambassador

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
Not applicable
Author

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
Partner Ambassador
Partner Ambassador

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
Not applicable
Author

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
Partner Ambassador
Partner Ambassador

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
Not applicable
Author

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
Partner Ambassador
Partner Ambassador

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
Not applicable
Author

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