Qlik Community

Qlik Sense App Development

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

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
simotrab
Not applicable

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.

Tags (2)
1 Solution

Accepted Solutions
sasiparupudi1
Not applicable

Re: Set analysis or if with measures.

May be try

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

5 Replies
arvind_patil
Not applicable

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

pathiqvd
Not applicable

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,

simotrab
Not applicable

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

sasiparupudi1
Not applicable

Re: Set analysis or if with measures.

May be try

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

passionate
Not applicable

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