Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

zagzebski
Contributor

Using a variable for Dimension in Aggregation

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?

3 Replies

Re: Using a variable for Dimension in Aggregation

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

then you can use that variable like this:

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

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



talk is cheap, supply exceeds demand
zagzebski
Contributor

Re: Using a variable for Dimension in Aggregation

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.

Not applicable

Re: Using a variable for Dimension in Aggregation

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.

Community Browser