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

Set modifiers question

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!

1 Solution

Accepted Solutions
sunny_talwar

Then you need this

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

View solution in original post

7 Replies
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
Partner - Champion III
Partner - Champion III

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]))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
lwestmaas
Contributor III
Contributor III
Author

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

Capture.PNG

When 2 & 4 are selected

Capture.PNG

lwestmaas
Contributor III
Contributor III
Author

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

Then you need this

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

lwestmaas
Contributor III
Contributor III
Author

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!