topic Re: Counting previous value in filter of getfieldselections in App Development

Counting previous value in filter of getfieldselections

NewToQlik — Sat, 16 Nov 2024 07:01:59 GMT

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?

Re: Counting previous value in filter of getfieldselections

PradeepReddy — Mon, 17 Dec 2018 04:01:49 GMT

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

Re: Counting previous value in filter of getfieldselections

NewToQlik — Mon, 17 Dec 2018 04:16:50 GMT

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?

Re: Counting previous value in filter of getfieldselections

PoonehTahernia — Mon, 17 Dec 2018 05:37:59 GMT

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.

Re: Counting previous value in filter of getfieldselections

PradeepReddy — Mon, 17 Dec 2018 05:39:08 GMT

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

Re: Counting previous value in filter of getfieldselections

NewToQlik — Mon, 17 Dec 2018 06:18:09 GMT

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

Re: Counting previous value in filter of getfieldselections

PradeepReddy — Mon, 17 Dec 2018 06:29:56 GMT

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

Re: Counting previous value in filter of getfieldselections

NewToQlik — Mon, 17 Dec 2018 06:40:40 GMT

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?

Re: Counting previous value in filter of getfieldselections

PradeepReddy — Mon, 17 Dec 2018 08:32:08 GMT

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

Re: Counting previous value in filter of getfieldselections

NewToQlik — Mon, 17 Dec 2018 06:54:39 GMT

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.

Re: Counting previous value in filter of getfieldselections

PradeepReddy — Mon, 17 Dec 2018 06:59:19 GMT

can you share your app?

Re: Counting previous value in filter of getfieldselections

NewToQlik — Mon, 17 Dec 2018 07:09:25 GMT

I am getting error saying 'Contents of the attachment doesn't match its file type'.

Re: Counting previous value in filter of getfieldselections

PradeepReddy — Mon, 17 Dec 2018 08:30:49 GMT

See the attachment..