Generate formulas in table

qlikviewalex — Sat, 16 Nov 2024 20:47:26 GMT

Hello,
I try to create an overview of key figures in a table. This could be a pivot table, for example. I still need two comment fields for the key figure. This table can look like this:

KPI	Comment1	Comment 2	Formula
turnover	a	a	2.015.200
ROI	s	s	215.000
Sum of 5+5	d	d	10
Sum of 4+4	f	f	8

The Formula column is to be calculated from different QlikView fields with different formulas. Does anyone know a good solution for this?

My idea is to load a new table for key figures into the data model. I create the new table in an Excel spreadsheet. In this table I define all columns and write the formulas I need. This will look like this.

KPI	Comment1	Comment 2	Formula
turnover	a	a	=sum(turnover)
ROI	s	s	=sum(NetIncome)/sum(Investment)
Sum of 5+5	d	d	=5+5
Sum of 4+4	f	f	=4+4

Now I have created the table in QlikView. For this I created a pivot with the columns KPI, Comment1 and Comment2. As formula I use the following function:

$(=Formula)

If I filter the table to one KPI it works. With several formulas this doesn't work anymore. Does anyone have an idea what I did wrong?

I hope someone of you can help me.
Greetings
Alex

Re: Generate formulas in table

felipedl — Mon, 27 May 2019 19:04:42 GMT

Hi,

You'd have to get a calculated dimension with ValueList like so:

=ValueList('Turnover','ROI','Sum of 5+5','Sum of 4+4')

This will create a dummy dimension with 4 text on it.
As for expression, you'd use:

pick
(
match
(
ValueList('Turnover','ROI','Sum of 5+5','Sum of 4+4'),
'Turnover',
'ROI',
'Sum of 5+5',
'Sum of 4+4'
),
$(v1),
$(v2),
$(v3),
$(v4)
)

Ive created the v1,v2,v3 and v4 variables like so:
v1: sum(value)
v2: 2
v3: 5+5
v4: 4+4

Resulting in:

topic Generate formulas in table in QlikView

Generate formulas in table

Re: Generate formulas in table