Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a chart displaying data from each of my fields. It's important that it stays as one single object as it will be used dynamically elsewhere.
'Year' is the key field that I want to show all possible related values for. However, there is no relationship between the other fields at all (at least not one that I want to show).
I've attached an excel doc, in the first tab is the way the data displays at the moment, and in the second tab is how I want it to display.
Basically, I want to remove duplicates and essentially show what would appear in a list box for each field, for each value in 'Year'.
Hope that makes sense! Any help appreciated.
Jess
Hi Varun,
Unless I'm missing something, the 'Multi List Box' in your application is just showing my data in a pivot table - which is what I don't want it to do!
Hi Jessica,
Please find the attached and let me know your opinion.
Thank you Rasly - that looks great!
Just trying to work out how you've done it
Have just realised it's all done in the script. Unfortunately I can't do that as I need my data model to stay the same
Hi Jessica,
Ok, then without changing the model, you wanted the same to be implemented in the UI itself?
If so, I will try and get back to you if there is any luck.
Hi Rasly,
That's right - preferably would like the layout to be the same as the 2nd tab.
Thanks for your help!
Hi Jessica,
Try a pivot table with calculated dimension:
=ValueLoop(1,$(=Max(Aggr(FieldValueCount($Field),$Field))))
Edit: Need this dimension too - $Field
and expression
=FieldValue($Field,ValueLoop(1,$(=Max(Aggr(FieldValueCount($Field),$Field)))))
to get:
=ValueLoop(1,30) | $Field | Section A | Section B | Section C | Section D | Section E | Section G | Year |
---|---|---|---|---|---|---|---|---|
1 | UK | Swindon | Bike | E54 | Citizen | Citizen Anon | 1 | |
2 | Newcastle | Car | B41 | 2 | ||||
3 | Cardiff | Q35 | 3 | |||||
4 | Edinburgh | S74 | ||||||
5 | Bristol | N14 | ||||||
6 | Liverpool | E41 | ||||||
7 | Brighton | E86 | ||||||
8 | Y24 | |||||||
9 | S72 | |||||||
10 | Q32 | |||||||
11 | N25 | |||||||
12 | R43 | |||||||
13 | Q21 | |||||||
14 | H47 | |||||||
15 | J65 | |||||||
16 | Z25 | |||||||
17 | T27 | |||||||
18 | R82 | |||||||
19 | W23 | |||||||
20 | F67 | |||||||
21 | A59 | |||||||
22 | T43 | |||||||
23 | L24 | |||||||
24 | D53 | |||||||
25 | A53 | |||||||
26 | V14 | |||||||
27 | D85 | |||||||
28 | Z68 | |||||||
29 | Y77 | |||||||
30 | C43 |
Cheers
Andrew
Hi Andrew,
Thanks so much for this.
Is there a way to include the 'Year' field in the expressions so that possible values are evaluated for each year
I would also need to be able to restrict which fields show as I have other fields in the script that I do not want to be included in the pivot table (apologies - I probably should have mentioned this in my initial query, but was trying to keep things simple!).
Thanks,
Jess
Hi Jessica,
Make your pivot table with these dimensions:
Year
=ValueLoop(1,$(=Max(Aggr(FieldValueCount($Field),$Field))))
=if($Field <> 'Year',$Field) // In the dimensions tab check Suppress When Value Is Null
Your expression is :
=FieldValue(if($Field <> 'Year',$Field),ValueLoop(1,$(=Max(Aggr(FieldValueCount(if($Field <> 'Year',$Field)),$Field)))))
Hopefully that will work for you.
cheers
Andrew
Year | =ValueLoop(1,30) | =if($Field <> 'Year',$Field) | Section A | Section B | Section C | Section D | Section E | Section G |
---|---|---|---|---|---|---|---|---|
1 | 1 | UK | Swindon | Bike | E54 | Citizen | Citizen Anon | |
2 | Newcastle | Car | B41 | |||||
3 | Cardiff | Q35 | ||||||
4 | Edinburgh | S74 | ||||||
5 | Bristol | N14 | ||||||
6 | Liverpool | E41 | ||||||
7 | Brighton | E86 | ||||||
8 | Y24 | |||||||
9 | S72 | |||||||
10 | Q32 | |||||||
11 | N25 | |||||||
12 | R43 | |||||||
13 | Q21 | |||||||
14 | H47 | |||||||
15 | J65 | |||||||
16 | Z25 | |||||||
17 | T27 | |||||||
18 | R82 | |||||||
19 | W23 | |||||||
20 | F67 | |||||||
21 | A59 | |||||||
22 | T43 | |||||||
23 | L24 | |||||||
24 | D53 | |||||||
25 | A53 | |||||||
26 | V14 | |||||||
27 | D85 | |||||||
28 | Z68 | |||||||
29 | Y77 | |||||||
30 | C43 | |||||||
2 | 1 | UK | Swindon | Bike | E54 | Citizen | Citizen Anon | |
2 | Newcastle | Car | B41 | |||||
3 | Cardiff | Q35 | ||||||
4 | Edinburgh | S74 | ||||||
5 | Bristol | N14 | ||||||
6 | Liverpool | E41 | ||||||
7 | Brighton | E86 | ||||||
8 | Y24 | |||||||
9 | S72 | |||||||
10 | Q32 | |||||||
11 | N25 | |||||||
12 | R43 | |||||||
13 | Q21 | |||||||
14 | H47 | |||||||
15 | J65 | |||||||
16 | Z25 | |||||||
17 | T27 | |||||||
18 | R82 | |||||||
19 | W23 | |||||||
20 | F67 | |||||||
21 | A59 | |||||||
22 | T43 | |||||||
23 | L24 | |||||||
24 | D53 | |||||||
25 | A53 | |||||||
26 | V14 | |||||||
27 | D85 | |||||||
28 | Z68 | |||||||
29 | Y77 | |||||||
30 | C43 | |||||||
3 | 1 | UK | Swindon | Bike | E54 | Citizen | Citizen Anon | |
2 | Newcastle | Car | B41 | |||||
3 | Cardiff | Q35 | ||||||
4 | Edinburgh | S74 | ||||||
5 | Bristol | N14 | ||||||
6 | Liverpool | E41 | ||||||
7 | Brighton | E86 | ||||||
8 | Y24 | |||||||
9 | S72 | |||||||
10 | Q32 | |||||||
11 | N25 | |||||||
12 | R43 | |||||||
13 | Q21 | |||||||
14 | H47 | |||||||
15 | J65 | |||||||
16 | Z25 | |||||||
17 | T27 | |||||||
18 | R82 | |||||||
19 | W23 | |||||||
20 | F67 | |||||||
21 | A59 | |||||||
22 | T43 | |||||||
23 | L24 | |||||||
24 | D53 | |||||||
25 | A53 | |||||||
26 | V14 | |||||||
27 | D85 | |||||||
28 | Z68 | |||||||
29 | Y77 | |||||||
30 | C43 |