topic Set analysis or if with measures. in App Development

Set analysis or if with measures.

simotrab — Thu, 03 Aug 2017 09:07:06 GMT

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( 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.

arvind_patil — Thu, 03 Aug 2017 09:17:56 GMT

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.

pathiqvd — Thu, 03 Aug 2017 09:41:15 GMT

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.

simotrab — Thu, 03 Aug 2017 09:42:20 GMT

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.

sasiparupudi1 — Sat, 05 Aug 2017 17:26:34 GMT

May be try

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

Re: Set analysis or if with measures.

passionate — Sat, 05 Aug 2017 19:30:58 GMT

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