Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
I've attached the app.
Thanks a lot.
May be try
count({<type={a},workerid={"=sum({<Year={2016}>}sale) > sum({<Year={2017}>}sale))"}>}workerid)
HI Simone,
(aggr(Sum ({<year={'Max(year)'}>}Sale),[workerid,type))-(aggr(Sum ({<year={'Max(year)-1'}>}Sale),[workerid,type))
Thanks,
Arvind Patil
Hi,
Try This,
count({<workerid={"=sum({$<type={'a'},year={'2016'}>}sale)>sum({$<type={'a'},year={'2017'}>}sale)"}>}distinct workerid)
Regards,
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])
May be try
count({<type={a},workerid={"=sum({<Year={2016}>}sale) > sum({<Year={2017}>}sale))"}>}workerid)
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