Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simotrab
Creator III
Creator III

Set analysis or if with measures.

Hi community,

I'm working on an app that makes me problem. The goal is to have a kpi that counts the worker that have sold "type a" things, and their sales of last year are stricly lower than sales of this year.

The data are those:

Load*Inline

[worker,workerid

a,1

b,2

c,3

d,4

];

Load*Inline

[workerid,sale,type,year

1,5,a,2016

1,4,a,2017

2,1,a,2016

2,8,a,2017

3,9,a,2016

3,2,a,2017

3,1,a,2017

4,4,a,2017

];

Putting on a table, with the if clause it is all fine, but in a KPI, it does not work and I do not know why.

The formulae of the kpi are

if

if( sum({$<type={"a"},year={"2016"}>}sale) > sum({$<type={"a"},year={"2017"}>}sale) ,count(workerid),0 )

set

count({$<type={"a"},year={"2016"},workerid={"$(sum({$<type={"a"},year={"2016"}>}sale) > sum({$<type={"a"},year={"2017"}>}sale))"}>}workerid)

kpi.PNG

I've attached the app.

Thanks a lot.

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

May be try

count({<type={a},workerid={"=sum({<Year={2016}>}sale) > sum({<Year={2017}>}sale))"}>}workerid)

View solution in original post

5 Replies
arvind_patil
Partner - Specialist III
Partner - Specialist III

HI Simone,

(aggr(Sum ({<year={'Max(year)'}>}Sale),[workerid,type))-(aggr(Sum ({<year={'Max(year)-1'}>}Sale),[workerid,type))


Thanks,

Arvind Patil

pathiqvd
Creator III
Creator III

Hi,

Try This,

count({<workerid={"=sum({$<type={'a'},year={'2016'}>}sale)>sum({$<type={'a'},year={'2017'}>}sale)"}>}distinct workerid)

Regards,

simotrab
Creator III
Creator III
Author

Hi Arvind,

thanks but unluckily it does not work, also because it sign errors. I've tried to correct them but the result is not useful

(to remove complexity, the year is a constant now, your solution were more elegant clearly)

aggr(Sum ({<year={"2017"}>}sale),[workerid,type])-aggr(Sum ({<year={"2016"}>}sale),[workerid,type])

sasiparupudi1
Master III
Master III

May be try

count({<type={a},workerid={"=sum({<Year={2016}>}sale) > sum({<Year={2017}>}sale))"}>}workerid)

passionate
Specialist
Specialist

Hi Simone,

You can try this by creating a flag in script.

Please find attached solution which is in qlikview.

but, the script will remain same for both.

Regards,

Pankaj