Dynamic variable name in pivot table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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
Representative	ProductLine	Variable
1	A	1_A
2	B	2_B
3	C	3_C
4	D	4_D
5	E	5_E

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

Variable = Representative&'_'&ProductLine
Variable2 =$(=('Representative&'_'&ProductLine'))
Representative	ProductLine	Variable	Variable 2
1	A	1_A	-
2	B	2_B	-
3	C	3_C	-
4	D	4_D	-
5	E	5_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'))
Representative	ProductLine	Variable	Variable 2
1	A	1_A	2000

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.

Re: Dynamic variable name in pivot table

marcus_sommer — Tue, 29 May 2018 09:20:53 GMT

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

topic Dynamic variable name in pivot table in QlikView

Dynamic variable name in pivot table

Re: Dynamic variable name in pivot table