Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
qlikviewalex
New Contributor III

Generate formulas in table

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:

KPIComment1Comment 2Formula
turnoveraa2.015.200
ROIss215.000
Sum of 5+5dd10
Sum of 4+4ff8

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.

KPIComment1Comment 2Formula
turnoveraa=sum(turnover)
ROIss=sum(NetIncome)/sum(Investment)
Sum of 5+5dd=5+5
Sum of 4+4ff=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

 

 

 

 

Tags (2)
Labels (3)
1 Solution

Accepted Solutions
Partner
Partner

Re: Generate formulas in table

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:

sample.png

1 Reply
Partner
Partner

Re: Generate formulas in table

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:

sample.png