Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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!

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Set modifiers question

Then you need this

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

View solution in original post

7 Replies
Highlighted

Re: Set modifiers question

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.

Highlighted
MVP
MVP

Re: Set modifiers question

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
Highlighted
Contributor III
Contributor III

Re: Set modifiers question

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.

Highlighted

Re: Set modifiers question

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

Highlighted
Contributor III
Contributor III

Re: Set modifiers question

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

Highlighted

Re: Set modifiers question

Then you need this

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

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Set modifiers question

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!