topic Using a variable for Dimension in Aggregation in QlikView

Using a variable for Dimension in Aggregation

zagzebski — Sat, 10 Aug 2013 18:56:33 GMT

I have the following expression:

Sum(
           Aggr(
              if(sum({<[Year] = {$(=Max([Year])-1)}>}[Commission])>0,
                 sum({<[Year] = {$(=Max([Year]))}>}[Commission]),0),
                             [Policy Type],[Customer Name],[Department Name])

This expression works fine. However where "Department Name" is I want a variable based on a cycle group (which is the dimension in this object). So each time the cycle is changed it is reflected via a variable in the expression above.

What is the best way of doing this?

Re: Using a variable for Dimension in Aggregation

Gysbert_Wassenaar — Sat, 10 Aug 2013 19:04:12 GMT

If you have a variable vField defined as something like this: =getcurrentfield(MyGroupNameHere)

then you can use that variable like this:

edit: to take field names with spaces or other weirdness into account try changing the variable to ='[' & getcurrentfield(MyGroupNameHere) & ']'

Re: Using a variable for Dimension in Aggregation

zagzebski — Sat, 10 Aug 2013 19:32:08 GMT

Thanks for the response. I am really close

It appears that it works for selections (within the cycle group) that do NOT have any spaces. It doesn't work for selections such as "Line of Business". I tried $([vRetensionDimension]) with brackets but that didn't seem to work.

Re: Using a variable for Dimension in Aggregation

Wed, 10 Jun 2015 07:43:33 GMT

Hello,
I created a group a cycle group, and tried to create the variable with getcurrentfield, but QV doesnt recognize the name of my group in the expression.