topic Re: Qlik Sense : How to restrict graph for 5 years in App Development

Qlik Sense : How to restrict graph for 5 years

Fri, 30 Jun 2017 10:03:58 GMT

Expression :

rangesum( below( count({ < EMP_HRIS = {"E"} >} [Emp Code]), 0, FYEAR))

count({<EMP_HRIS = {"L"} >} [Emp Code] )

Dimension : FYEAR

In the above graph I want to restrict to view only for 5 years ,

As my count is accumulating year wise , if I restrict at dimension (Year) level some data is missing see below screen shot.

Dimension :

=if(FYEAR>=max(total FYEAR-5),FYEAR)

Using same Expression

Now it’s showing wrong values

Re: Qlik Sense : How to restrict graph for 5 years

kaushiknsolanki — Fri, 30 Jun 2017 11:06:46 GMT

Try the limitation option in dimension.

Keep it to Fixed 5.

and sort your chart by year dimension.

Regards,

Kaushik Solanki

Re: Qlik Sense : How to restrict graph for 5 years

Fri, 30 Jun 2017 11:14:12 GMT

This option is in Qlikview not in QlikSense....

Where is this option in Qlik Sense ?

Re: Qlik Sense : How to restrict graph for 5 years

kaushiknsolanki — Fri, 30 Jun 2017 11:18:41 GMT

The option is under dimension.

Regards,

Kaushik Solanki

Re: Qlik Sense : How to restrict graph for 5 years

Fri, 30 Jun 2017 11:43:02 GMT

If I am using below options .. my Data is showing wrongly... see below graph which is showing wrong values.

For 2017 count should be 4277 not 2434 , Pls see my expression (above) as count is accumulating per year

Re: Qlik Sense : How to restrict graph for 5 years

sunny_talwar — Fri, 30 Jun 2017 13:13:15 GMT

How about using this

=(RangeSum(Below(Count({< EMP_HRIS = {"E"} >} [Emp Code]), 0, FYEAR)) - Count({<EMP_HRIS = {"L"}>} [Emp Code])) * Avg({<Year = {"$(='<=' & (Max(FYEAR)-1) & '<=' & Max(FYEAR))"}>} 1)

Re: Qlik Sense : How to restrict graph for 5 years

Anonymous — Sun, 02 Jul 2017 07:14:46 GMT

Hi Mahi,

You are accumulating values from your business starting years, if you want to display latest 5, in your dimension you need to connsider all the years till 2013 as yr 2013, then the expression will give correct numbers.

Ex: if(FYEAR='2011' or FYEAR='2012' or FYEAR='2013','2013',FYEAR)

Regards,

Sadasiva

Re: Qlik Sense : How to restrict graph for 5 years

Tue, 04 Jul 2017 12:35:45 GMT

Its not working ,

Tried in other way... Its working ...

=if(FYEAR<max(Total FYEAR-4),max(Total FYEAR-4),FYEAR)

Re: Qlik Sense : How to restrict graph for 5 years

Wed, 05 Jul 2017 07:03:41 GMT

I am able to restrict for 5 years with the below code ... but additionally its showing 2012 year which I dont want to show in my graph , How to hide or avoid 2012 in the graph.

Dimension :

=if(FYEAR<max(Total FYEAR-5),max(Total FYEAR-5),FYEAR)

Expression:

Existing :

rangesum( above ( count({ < EMP_HRIS = {"E"} >} [Emp Code]), 1, FYEAR))

(

rangesum( below ( count({ < EMP_HRIS = {"L"} >} [Emp Code]), 0, FYEAR))

rangesum( below( count({ < EMP_HRIS = {"LJ"} >} [Emp Code]), 0, FYEAR))

)

Joiners:

(count({<EMP_HRIS = {"E"} >} [Emp Code] )

count({<EMP_HRIS = {"LJ"} >} [Emp Code] )

)

Leavers:

if( FYEAR>=max(Total FYEAR-5),

count({<EMP_HRIS = {"L"} >} [Emp Code] )

Kindly provide any suggestions here...

Re: Qlik Sense : How to restrict graph for 5 years

Anonymous — Wed, 05 Jul 2017 07:19:12 GMT

Hi Mahi,

=if(FYEAR<max(Total FYEAR-4),max(Total FYEAR-4),FYEAR),

This will remove 2012.

Regards,

Sadasiva

Re: Qlik Sense : How to restrict graph for 5 years

Wed, 05 Jul 2017 07:22:38 GMT

The same code working fine in Qlikview ,

The only one difference is did suppress null values in Qlikview.

I dont find option " Suppress Null values " in Qlik Sense , the same did in Qlikview.

Re: Qlik Sense : How to restrict graph for 5 years

Wed, 05 Jul 2017 07:33:35 GMT

Thanks siva , ITs working fine...