Solved: Count something diferent when selected - Qlik Community

moda1023 · ‎2018-09-21

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 Welding	ID Painting	Workstation Welding	Workstation Painting	Date
24546	12345	67890	Welding 2	Painting 1	15.03.2018
12341	98765	67890	Welding 2	Painting 2	15.03.2018
34577	12345	54321	Welding 1	Painting 3	15.03.2018
23455	98765	67890	Welding 2	Painting 2	15.03.2018
89675	67890	12345	Welding 3	Painting 1	16.03.2018
62953	54321	98765	Welding 2	Painting 1	16.03.2018
25677	54321	12345	Welding 1	Painting 2	16.03.2018
53468	67890	98765	Welding 3	Painting 3	16.03.2018
43563	98765	54321	Welding 2	Painting 1	17.03.2018
74567	12345	67890	Welding 3	Painting 1	17.03.2018
34763	98765	54321	Welding 2	Painting 3	17.03.2018
34724	12345	67890	Welding 1	Painting 2	17.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.

jensmunnichs · ‎2018-10-03

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

vishsaggi · ‎2018-09-21

May be try this?

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

rubenmarin · ‎2018-09-22

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 · ‎2018-10-02

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 · ‎2018-10-02

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 · ‎2018-10-02

Can you try below:

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

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

jensmunnichs · ‎2018-10-03

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 · ‎2018-10-03

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 · ‎2018-10-03

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 · ‎2018-10-03

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.