Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
NewToQlik
Creator
Creator

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
Specialist II
Specialist II

See the attachment..

View solution in original post

12 Replies
PradeepReddy
Specialist II
Specialist II

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

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
Contributor II
Contributor II

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
Specialist II
Specialist II

are you getting the correct results, when you use the expression I provided?
Check the date format once..
NewToQlik
Creator
Creator
Author

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

PradeepReddy
Specialist II
Specialist II

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

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
Specialist II
Specialist II

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

NewToQlik
Creator
Creator
Author

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.