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: 
moda1023
Contributor III
Contributor III

Count something diferent when selected

Hello everyone :-),

so probably there is a simple solution for my problem but i wasnt able to find one yet.

In fact my example for my problem was not that good, so here is another approach:

   

Order Nr.ID WeldingID PaintingWorkstation WeldingWorkstation PaintingDate
245461234567890Welding 2Painting 115.03.2018
123419876567890Welding 2Painting 215.03.2018
345771234554321Welding 1Painting 315.03.2018
234559876567890Welding 2Painting 215.03.2018
896756789012345Welding 3Painting 116.03.2018
629535432198765Welding 2Painting 116.03.2018
256775432112345Welding 1Painting 216.03.2018
534686789098765Welding 3Painting 316.03.2018
435639876554321Welding 2Painting 117.03.2018
745671234567890Welding 3Painting 117.03.2018
347639876554321Welding 2Painting 317.03.2018
347241234567890Welding 1Painting 217.03.2018

So imagine a bike factory, you get a order, then along the process you pass through the welding and painting workstations.

People work on different workstations every day.

So as you see in the attached file, first i try to display how much every person worked so the Count(ID Welding) + Count(ID Painting).

Then i want to display the  Worstation Welding over the ID Welding and the Workstation Painting over the ID Painting.

I was aiming to click on one specific ID in the first chart, so the other two charts should display me how much this person worked on the welding workstation and on the painting workstation.

I'm struggling to find a proper dimension in the first chart. Even tried loading data in different ways...

Kind Regards

Daniel

Changed by Daniel B.

1 Solution

Accepted Solutions
jensmunnichs
Creator III
Creator III

Hey, check the attached file.

I hope this is what you're looking for. I did this:

1. Load in a separate table in script that contains 2 lines for every order number: 1 with the associated Welding ID, 1 with the associated Painting ID. Both ID types are now just called 'ID'.

2. The first chart now uses 'ID' as dimension, which should always contain all IDs. The expression in the first chart is simply 'Count(ID)'.

3. The other 2 charts first check whether something has been selected for 'ID'. If nothing is selected, it will simply count the respective ID and split the bars by workstation number. If an ID has been selected, it will show that count, but only for the IDs where the selected ID is the same as the respective welding/painting ID.

Only 2 problems with this (that I can see):

- Adds an extra table to the data model that can be quite big depending on your data set. If this is a problem, I think it's possible to only load unique ID's to this table. However, you would then have to go back to 'Count([Welding ID])+Count([Painting ID])' in your first chart. This would make it so that the extra table only serves as a list of unique ID's to use as a dimension for your first chart.

- Making a selection in [Welding ID] or [Painting ID] could lead to unexpected results. Use [ID] instead (see the listbox I added or just click in the first chart). If you need to prevent users from making a wrong selection, you might have to do something with triggers, but I don't have much experience with this so I left that out for now.

Let me know if you need any more help, I hope this does the trick for you

(ps. the added excel file is the same as your data, just didn't have the text file you used)

View solution in original post

15 Replies
vishsaggi
Champion III
Champion III

May be try this?

= Count(IF(GetFieldselection(ID) = SubID1, USA, USA))

rubenmarin

Hi Daniel, maybe it's because you real table is more complex, but i think you can do just:

- USA: Count(USA)

- Europe: Count(Europe)

- All: Count(USA)+Count(Europe)

moda1023
Contributor III
Contributor III
Author

Thanks for your answer!

I already tried this, but both columns have noramally data. Besides i need a connection between SubID1/USA and SubID2/Europe.

moda1023
Contributor III
Contributor III
Author

Thanks for the tip with GetFieldselections!

But this would count USA everytime, even when the if-clause is not true.

=IF(GetFieldselections(ID)=SubID1, Count(USA), Count(USA+Europe))

Would that make sense?

vishsaggi
Champion III
Champion III

Can you try below:

= Sum(Aggr(Count(IF(GetFieldselections(ID) = SubID1, USA,

                                   IF(GetFieldSelections(ID) = SubID2, Europe, 1))), ID))

jensmunnichs
Creator III
Creator III

I'm a bit confused about what you're trying to achieve, because as far as I can tell this:


" Count(IF(SubID1=ID,USA))

and Count(IF(SubID2=ID, Eurpe))."

Already achieves this:

"But how do i manage to count everything in USA and Europe before choosing a ID in the first chart?"

Obviously hard to demonstrate with a limited amount of data, but even when I add more lines of data it works as expected.

I'm guessing this is because you tried to simplify your problem, but could you be a little bit more specific? Maybe create something that demonstrates what you're trying to do?

moda1023
Contributor III
Contributor III
Author

Thanks, i think this did the trick!

I think i'm having another problem with the data, but would need to open another topic for that.

moda1023
Contributor III
Contributor III
Author

Sorry, yeah you're right.

So i tried to find another example that suits better to find a solution to my problem and updated it in the description.

jensmunnichs
Creator III
Creator III

Alright so I think I understand what you mean now. Essentially you want your first chart to have a dimension that contains all unique IDs from [ID Welding] and [ID Painting], and count the ID if it was in either one of those fields (and count it twice if it's in both fields, right?). I actually had a similar problem recently. I found a solution, but it wasn't pretty. I'll see if I can figure something out for you.