Count something diferent when selected

[moda1023]

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.

[moda1023]

Exactly, in the first chart as a dimension i need all unique IDs. Then count how often they appeared in either workstation welding or painting (cant to both at the same time). So for example ID 12345 would count 6 in total and have then 4 in workstation welding and 2 in workstation painting. The chart worstation welding station/welding id would then display these 4 as:

2 in Welding 2

1 in Welding 3

1 in Welding 1

and the same for painting.

And when no ID is selected it should show me in the welding station/welding id chart the overall amount on the different stations. The same for the painting.

I'm already trying about 2 weeks to solve this ...

[jensmunnichs · Accepted solution]

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)

[moda1023]

Thank you very much!

Finally a solution

Do you have any idea how i could only load unique ID's? I have no idea how i could connect it to the "Original" table? (missing key)

With the amount of data i have, it would really improve some performance issues.

[jensmunnichs]

Hey,

I thought I had the solution for this, but it seems your situation is a bit different from mine, making it more complicated than I thought it would be. I'll give it a shot but I'm not sure if I'll be able to help you with this.. Sorry

[jensmunnichs]

So I figured something out, but, there is probably a much better solution for this. If there is though, I haven't found it yet.

What I've done:

1. Instead of using a table that contains 2 rows for every order in the data model, I made this table temporary (so it still gets loaded in but it's not in the final data model), and then counted the order numbers per ID, as we're currently doing in the first chart. This data can then be used very easily to create the first chart, showing total Orders per ID. It also gives us a list of unique IDs.

Where things get ugly is that I had to load in ID as ID Welding to create an association between this table and the original table. If we don't do this, the .qvw becomes incredibly slow, but this association obviously isn't a correct one. You'll see this in the table that was in your file when you select an ID from the listbox.

2. To account for the 'bad' association when making the other charts, we'll have to use set analysis that disregards the current selection, and then use the current selection to identify the correct Welding or Painting ID's, similar to what I did last time. The only difference is that I had to add a '1' to the set analysis (meaning disregard current selection). Any other objects made in this document would have to be made similarly.

3. I wasn't sure whether you needed the table box to work properly, so I fixed that too. I created a straight table with all your columns as dimension, and an expression that counts the order numbers if the selected ID is equal to either the Welding or Painting ID. This adds a column with 0s and 1s to the end of the table. Any row where this column is 0 won't show in the table. I then hid this column so the table is exactly the same as the one in your original file.

Sorry for the wall of text, but what this comes down to is:

- If you want to use the unique ID field as a dimension, you'll have to create the data for that object in the script, to avoid having a massive table in your data model. This will still require loading in the data once, increasing reload times, but it should avoid performance issues for the end user.

- If you want to use the ID field as a selection, you'll have to use set analysis similar to what I used in the bottom 2 charts.

Hope this helps, and if someone knows a better solution to this issue please let me/us know, because this seems very messy.

EDIT: Forgot to add the file

[moda1023]

Thank you very much for your effort!

I still did not have time to try this out. In the next couple of days i will try to test ist