Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Oguzhan
Contributor III
Contributor III

Qlik Sense: Compare dimension values to another dimension

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'

TasknumberStatusOrder number
1

done

0111
2done0111
3done0111
4done0112
5done0112
6open0112
7open0112

 

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 numberStatus Order
0111done
0112open

 

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.

Labels (2)
1 Solution

Accepted Solutions
Taoufiq_Zarra

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

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

9 Replies
Taoufiq_Zarra

@Oguzhan 

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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
asinha1991
Creator III
Creator III

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)

 

 

 

Oguzhan
Contributor III
Contributor III
Author

@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

PriorityTasknumberStatusOrder number
K01

done

0111
K12done0111
K13done0111
K04done0112
K15done0112
K16open0112
K17open0112
K18confirmed0112

 

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)

 

Taoufiq_Zarra

@Oguzhan 

can you share the expected output from this sample ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Oguzhan
Contributor III
Contributor III
Author

@Taoufiq_Zarra .

I expect something similar like this.

PriorityOrder numberStatus Order
K00111done
K00112open

 

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'

 

Taoufiq_Zarra

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

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Taoufiq_Zarra

Capture.PNG

@Oguzhan  output

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Oguzhan
Contributor III
Contributor III
Author

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

Taoufiq_Zarra

@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'))

Capture.PNG

otherwise I'll search for the best possible explanation.

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉