Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
ekaponkratova
Creator
Creator

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
Partner

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
Partner

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

ekaponkratova
Creator
Creator
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
Partner

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 🙂 

ekaponkratova
Creator
Creator
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
Partner

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.

ekaponkratova
Creator
Creator
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
Partner

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))
ekaponkratova
Creator
Creator
Author

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