COUNT multiple specific values with multiple conditions
Hi, I've been using Qlik Sense for about one month and recently I've encountered a problem which I've been trying to solve since last week but until now still couldn't, so I really hope someone can help me here.
So I have this data shown in the picture above with 7 columns, and I need to get a graph as shown. The total quantity can be obtained from the CreUser data, and for the Hose, Join, Assy, if there is data for that particular field, then it is considered settled for that field. If it is NULL, then it means unsettled for that particular field. Eg: there is four NULLs in the JoinUser column and another 7 rows with data. Hence, in the graph, the settle quantity for Join field is 7 and unsettled is 4. In the end, the sum of settle and unsettle qty for all the fields must be equal to the total quantity.
What I've done is I created another four new columns in qlik sense to input '0' and '1' to indicated the presence of data in those four fields (Hose, Join, Assy, Complete), and then i concatenate the data, for eg: 1-0-0-0. For the process, it is for sure that they will start from Hose, then go to Join, then Assy and finally complete. So according to binary number, the possibilities will only be 1-0-0-0, 1-1-0-0, 1-1-1-0, 1-1-1-1. I've tried using all the commands that i found from the internet until I'm confused myself but I still can't get the graph out. Can someone help me? Thank you in advance !!!
I tried your solution and I really got the graph that I wanted. However, I wanna ask for the dimension, is it a must to use the valuelist()? Is there a way to get the graph by using the data from the table for the dimension? Because besides the status, I still need to show other alternative dimensions, for eg: by YearMonth and by Machine. If I use the valuelist function, then my YearMonth and Machine wouldn't show out. Sorry I forgot to include the column of Date and Machines in the example table above.
Thank you very much !!!! I've tried using the crosstable and I got it. But since I was told not to unlock the script for the published version, so I'll just mark the previous one as the solution. For the previous solution, I was able to show the YearMonth by using the filter as well. Anyway, really thanks a lot for your help !!!