Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewalex
Contributor III
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

 

 

 

 

Labels (3)
1 Solution

Accepted Solutions
felipedl
Partner
Partner

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

View solution in original post

1 Reply
felipedl
Partner
Partner

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

View solution in original post