Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
NewToQlik
New Contributor III

Counting previous value in filter of getfieldselections

Hi all,

I have a filter with years 2017,2018,2019. For example when I select year 2018, I want to be able to compute the count(ID) of the previous year (2017) as well.

Any idea on how to achieve this?

Labels (1)
1 Solution

Accepted Solutions
pradeepreddy
Valued Contributor II

Re: Counting previous value in filter of getfieldselections

See the attachment..

12 Replies
pradeepreddy
Valued Contributor II

Re: Counting previous value in filter of getfieldselections

try something like this..
Previous Year (PY) : Count(distinct {<Year={'$(=max(Year)-1)'}>}ID)
Current Year (CY) : Count(distinct ID)
NewToQlik
New Contributor III

Re: Counting previous value in filter of getfieldselections

Thank you for your reply. 

I have modified it to the following:
Count(distinct {<DATE={"$(=max(DATE)-365)"}>}ID)

But results shows 30/12/1899 no matter what I select. Any ideas?

PoonehTahernia
New Contributor II

Re: Counting previous value in filter of getfieldselections

you can use chart Function that named Above() , if you set year As dimension, and in Expression write Above(count(ID)), it calculate the previous count (ID) according to value of dimension that you set.

fist of all I suggest you to try it in the straight chart with year as dimension and 2 Expression

1- count(ID)

&

2-  Above(count(ID))

and check and see does it work well for you or not.

 

pradeepreddy
Valued Contributor II

Re: Counting previous value in filter of getfieldselections

are you getting the correct results, when you use the expression I provided?
Check the date format once..
NewToQlik
New Contributor III

Re: Counting previous value in filter of getfieldselections

I am getting '0' even when I have selected the dates

pradeepreddy
Valued Contributor II

Re: Counting previous value in filter of getfieldselections

Sample Data...

Test:
Load *,
Year(T_Date) as T_Year;
Load * inline
[
ID,T_Date
1,2017-10-20
2,2017-09-20
3,2017-03-15
4,2017-02-13
5,2018-01-02
6,2018-01-01
7,2016-02-20
8,2016-05-05
];

Expression..
Previous Year (PY) : Count(distinct {<T_Year={'$(=max(T_Year)-1)'}>}ID)
Current Year (CY) : Count(distinct ID)

Filter (List box) : T_Year
NewToQlik
New Contributor III

Re: Counting previous value in filter of getfieldselections

I will be using the dates as a filter. This is what it will look like 

Test:
Load *,
Year(T_Date) as T_Year;
Load * inline
[
ID1,T_Date
1,2017-10-20
2,2017-10-20
3,2017-10-20
4,2017-10-20
5,2018-10-20
6,2018-10-20
7,2016-10-20
8,2016-10-20
];

Filter box : T_Date
Count(distinct ID)
Count(distinct {<T_Date={'$(=max(T_Date)-365)'}>}ID1)

The second expression always gives a value of '0'. Any solutions?

pradeepreddy
Valued Contributor II

Re: Counting previous value in filter of getfieldselections

if you are using Date field as filter, then try something like this...
Count({<T_Year={'$(=Year(max(T_Date))-1)'}>}ID

NewToQlik
New Contributor III

Re: Counting previous value in filter of getfieldselections

It still shows '0'. I am trying to only select 1 date in the filter box. This expression will work if I select 2 dates.