7 Replies Latest reply: Mar 15, 2017 12:27 PM by Lucas Westmaas RSS

    Set modifiers question

    Lucas Westmaas

      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!

        • Re: Set modifiers question
          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.

            • Re: Set modifiers question
              Lucas Westmaas

              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.

            • Re: Set modifiers question
              Jonathan Dienst

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