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 FOR THE GRAPH doesnt work

Thanks to Radoresk

at (https://community.qlik.com/t5/Qlik-Community-Help-Discussions/Set-analysis-show-only-the-current-yea...) we fixed one part.

Now, if I want to show the same calculation but as a trendline showing MoM trend, the below formula works but it changes as soon as a person made a selection. How could I make the graph static?

 

count({1}{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({1}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))

1 Solution

Accepted Solutions
RadovanOresky
Partner Ambassador
Partner Ambassador

Hi, so in the end it was the IF statement inside of the Aggr.

 

count( { 1 <
    	[TimeStamp.Calendar.Year] = {"$(=year(today())-1)"}
      , 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())-1)"}
      , vessel_crane -= {"=len(trim(vessel_crane))=0"}
      , visit -= {"=len(trim(visit))=0"}
      , CRANE -= {"=len(trim(CRANE))=0"}
      >} aggr( if(((only( {1} t_max) - only( {1} t_min) )*24) <0,0,(only( {1} t_max) - only( {1} t_min) )*24)
            , vessel_crane )
	)

This did the trick in the sample app. Hope it will work in yours too.

But, I have to say that I did not check for the relevance of those numbers.

View solution in original post

11 Replies
Anonymous
Not applicable
Author

Interestingly, when you use the below formula to plot the last year, it doesn't work

count({1}{1<[TimeStamp.Calendar.Year] = {"$(=year(today())-1)"},type={'move'},vessel_crane-={"=len(trim(vessel_crane))=0"},
visit-={"=len(trim(visit))=0"},CRANE-={"=len(trim(CRANE))=0"}>} container)
/sum({1}distinct {1<[TimeStamp.Calendar.Year] = {"$(=year(today())-1)"},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))

sunny_talwar

You don't want this expression to change based on any selection made in the dashboard?
Anonymous
Not applicable
Author

q_10.png

I have a KPI and graph. The KPI will change depending on a date selection and the graph, no.

sunny_talwar

But without selection the data is showing up right or do you see issues even when no selections are made?
RadovanOresky
Partner Ambassador
Partner Ambassador

My proposed solution was to add the same SET into Aggr() function as well.

Don't know why it does not work, as it works for me on some other example.

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

 

Can you maybe post some sample data?

Anonymous
Not applicable
Author

Without the selection, everything is working just fine!

Anonymous
Not applicable
Author

Hold on.

Anonymous
Not applicable
Author

Here you go - https://drive.google.com/drive/folders/1rzos2xzZfWuDOjfPqJK3VrxTU7kUkrqT?usp=sharing

Sorry for uploading at Drive, but I cannot upload in this new interface. 

RadovanOresky
Partner Ambassador
Partner Ambassador

Hi, so in the end it was the IF statement inside of the Aggr.

 

count( { 1 <
    	[TimeStamp.Calendar.Year] = {"$(=year(today())-1)"}
      , 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())-1)"}
      , vessel_crane -= {"=len(trim(vessel_crane))=0"}
      , visit -= {"=len(trim(visit))=0"}
      , CRANE -= {"=len(trim(CRANE))=0"}
      >} aggr( if(((only( {1} t_max) - only( {1} t_min) )*24) <0,0,(only( {1} t_max) - only( {1} t_min) )*24)
            , vessel_crane )
	)

This did the trick in the sample app. Hope it will work in yours too.

But, I have to say that I did not check for the relevance of those numbers.