Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have following situation: I have a table like below, and I need to compare the status value with a order number and if all order numbers have the status "done" then I need QlikSense to display 'done' otherwise the status should say 'open'
Tasknumber | Status | Order number |
1 | done | 0111 |
2 | done | 0111 |
3 | done | 0111 |
4 | done | 0112 |
5 | done | 0112 |
6 | open | 0112 |
7 | open | 0112 |
I want to show the Order number in a table like this. Order number 0111 is done because all tasks underneath the Order are done. Order number 0112 is open because 2 tasks are done but there are still 2 tasks open so the overall status is open.
Order number | Status Order |
0111 | done |
0112 | open |
So far I have tried it with.
Aggr(If(Status='done', 'done', 'open'), Order number,Status)
but that didn't work as expected.
Thank in advance for any help.
@Oguzhan in this case you need to ignore Priority in measure like
in dimension
=if(Priority='K0',Priority) and chek Supress when value is null
and dimension Order Number
and for measure :
if(count({<Priority=,[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',Status)
output like :
you can try for example :
in dimension : Order Number
In expression :
if(count({<[Order number]={"=count({<Status={'open'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',Status)
output:
What Taoufiq suggested should work, for some reason if you want it to be a dimension and not measure, you can do this
if(aggr(count({<Status={'open'}>} Tasknumber),[Order number])>=1,'open',Status)
@Taoufiq_Zarra Thanks for your help. Your code seems to work, if there are only 2 type of status.
How do I handle this code if there are more than 2 statuses like 'open' , 'done' , 'confirmed'
I also added the dimension priority, and for me only K1 Priority is neccessary
Priority | Tasknumber | Status | Order number |
K0 | 1 | done | 0111 |
K1 | 2 | done | 0111 |
K1 | 3 | done | 0111 |
K0 | 4 | done | 0112 |
K1 | 5 | done | 0112 |
K1 | 6 | open | 0112 |
K1 | 7 | open | 0112 |
K1 | 8 | confirmed | 0112 |
I modified your code like this, but it doesn't give the correct status for the whole dataset.
if(count({<Priority={'K1'},[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',Status)
can you share the expected output from this sample ?
I expect something similar like this.
Priority | Order number | Status Order |
K0 | 0111 | done |
K0 | 0112 | open |
Order number 0111 is closed because all 'K1' task are closed.
Order number 0112 is open because there are still 'open' and 'confirmed' tasks
The logic in my table is like :
Priority 'K0' is my so called head order and the 'K1' tasks are "subtasks".
An order of the Priority K0 is fullfilled and done if all the 'K1' task lying underneath the head order are closed.
During the process the "subtasks" can have the statuses 'open' or 'confirmed'
@Oguzhan in this case you need to ignore Priority in measure like
in dimension
=if(Priority='K0',Priority) and chek Supress when value is null
and dimension Order Number
and for measure :
if(count({<Priority=,[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',Status)
output like :
@Oguzhan output
@Taoufiq_Zarra Why can't I write an alternate Status like, do I have to refer to the dimension Status?
if(count({<Priority=,[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open','closed')
If I write 'closed' instead of Status at the end of the code it doesn't work anymore.
@Oguzhan you can always do that.
if(count({<Priority=,[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',if(Status='done','closed'))
otherwise I'll search for the best possible explanation.