5 Replies Latest reply: Aug 5, 2017 3:30 PM by pankaj thakur

# 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:

[worker,workerid

a,1

b,2

c,3

d,4

];

[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)

I've attached the app.

Thanks a lot.

• ###### Re: Set analysis or if with measures.

HI Simone,

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

Thanks,

Arvind Patil

• ###### Re: Set analysis or if with measures.

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])

• ###### Re: Set analysis or if with measures.

May be try

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

• ###### Re: Set analysis or if with measures.

Hi,

Try This,

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

Regards,

• ###### Re: Set analysis or if with measures.

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