Set modifiers question

[lwestmaas]

Hello,

I am new to Qlik. I have a line graph with the dimension Year and am trying to show three different measures:

(1) The average for the entire dataset across years (with some selections allowed)

(2) The average for a single analytic unit - let's sale a sales office - across years, which only appears if a single unit is selected

(3) The average for other units that share a specific characteristic across years, also only appearing if a single unit is selected (with some selections allowed)

For example, say I want to measure sales of product P by office O, which is in group G.

For (1), I'm using this: avg({$<[Product]={P}, [Unit]=>} [Sales])

For (2), I'm using this: if(count(distinct([Office]))=1,avg({$<[Product]={P}>} [Sales]))

I'm stuck on 3, however. I think I need to have the set analysis contain something effectively saying, all units in the dataset for which the group is equal to the mode of group among the selected units. But I have not figured out how to insert an aggregation variable into a set modifier.

Can anyone help? Thanks in advance!

[sunny_talwar]

It might be easier to help if you can share a sample or sample data and explain what you wish to get out as result.

[jonathandienst]

Perhaps this:

    If(Count(distinct Office) = 1, avg({$<[Product] = {P}, Unit = E(Unit)>} [Sales]))

    or

    If(Count(distinct Office) = 1, avg({$<[Product] = {P}, Unit = E(Unit), Office>} [Sales]))

[lwestmaas]

Sure. I've also just realized a mistake in my original; "Unit" in the first line of code should say "Office".

Office      Group             Product          Sales

1             G                   P                    15

2             H                   P                    25

3             G                   P                    35

1             G                   Q                    27

4             H                   P                    20

5             G                   P                    13

(1) takes the average of all product P lines (so all lines except the product = Q line), which comes out to 36.

(2) takes the average of product P lines for a given office, if only one office is selected.

(3) should take the average sales of product P for the offices in group G when offices 1, 3, or 5 are selected (which is 21), and for the offices in group H when offices 2 or 4 are selected (which is 22.5).

Basically I am showing how the office did relative to the entire company and relative to other similar offices.

[sunny_talwar]

This expression should work, I think

=Avg({<Product = {'P'}>}Sales)

When 1, 3, & 5 are selected

When 2 & 4 are selected

[lwestmaas]

Hi Sunny,

Thanks for your help. The issue is that I don't want to have to select the other offices in the group in order to show the correct average. So if ONLY office 2 is selected, then the measure should show 22.5 even though office 4 is not selected.

Lucas

[sunny_talwar · Accepted solution]

Then you need this

=Avg({<Product = {'P'}, Office, Group = p(Group)>}Sales)

[lwestmaas]

I think that did it! Tested it on a few lines in my actual data and it came out with the correct answer each time. Thanks so much!