Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Dynamic variable name in pivot table

Hello,

I have a pivot table that show the margins per product line and sales representative. However, for the forecast I would like to have something dynamic where during my presentation the sales representative could say which margin or volume he is going to do and I could change a variable specific for the key combination of Representative&ProductLine.

In order to do that, I have created through a loop, hundreds of variables that - when changed - will replace the margin loaded in the script.

The idea was to have a 'real time' replacement of the data in the database based on these variables.

So the problem to create all these variables was solved but the problem now was to use it in a pivot table - and how to apply it in a way that when I filter 3 salesrepresentative he will use the variables of all these three representatives - again, like he have replaced the original column in the database with the data in the variable.

That said, I have created a pivot first using Representative&'_'&ProductLine as key, and the result was the one that I was expecting:

  

Variable = Representative&'_'&ProductLine
RepresentativeProductLineVariable
1A1_A
2B2_B
3C3_C
4D4_D
5E5_E

However, when I try to use =$(=('Representative&'_'&ProductLine')) the column comes blank

   

Variable = Representative&'_'&ProductLine
Variable2 =$(=('Representative&'_'&ProductLine'))
RepresentativeProductLineVariableVariable 2
1A1_A-
2B2_B-
3C3_C-
4D4_D-
5E5_E-

However, when I filter a specific representative and productline, then the column is populated accordingly to the variable created:

   

Variable = Representative&'_'&ProductLine
Variable2 =$(=('Representative&'_'&ProductLine'))
RepresentativeProductLineVariableVariable 2
1A1_A2000

This is not a set analysis issue since I have already tried using it.

That said, I kindly request your knowledge to help me through it.

Thanks.

1 Reply
Highlighted
MVP & Luminary
MVP & Luminary

I doubt that you get this approach ever to work. Calculations within a $-sign expansion are global and not related to the dimensions of your object - and in this global context you would also need aggregations for the included fields because without they return NULL if a field had more than one possible value.

An alternatively for your case might be Inputfields (Insert a string value to multiple InputField rows) or probably even better the use of any WriteBack approach to a database like SQL Writeback from QlikView extension object.

Nothing of them is easy to implement and therefore it might be considerable to buy such a solution. AFAIK there are multiple partners which offer such tools.

- Marcus