Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ekaponkratova
Creator
Creator

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
Partner

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

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?

ekaponkratova
Creator
Creator
Author

Without the selection, everything is working just fine!

ekaponkratova
Creator
Creator
Author

Hold on.

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

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.