Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Search instead for
Did you mean:
Creator

## Row level calculation in a table

Hi,

I need help to create this table in Qlik Sense. The Row A and B are coming from source, and I need to derive the row (highlighted in yellow) in Qlik using measure expression. Below the highlighted rows in yellow are the excel formula to drive the row-wise calculation.

I need to do it in the table (chart) only, not in the script. Thank you for your time and support in advanced!

Thanks,

SK

Labels (1)
• ### General Question

2 Solutions

Accepted Solutions
Partner - Creator

I think it's better to use "Set analysis" than using IF statements.

1. My Test dataset is

Test:
Load * Inline [
Type, Category, Value
A, 1, 1864.748611
A, 2, 1905.748611
A, 3, 1906.998611
A, 4, 1729.998611
A, 5, 1695.998611
B, 1, 6.51791423
B, 2, 13.07734798
B, 3, 19.1
B, 4, 25.9356
B, 5, 32.5306];

2. Table chart dimension is

=ValueList('A','B','Monthly(B)','Average rate of A & B')

3. Table chart Measure expression

: Simply use the "Above" function representing the previous row and the "Column" function specifying the column location. There are five measure in the chart.

1) Measure 1 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {1}, Type = {'A'} >} Sum(Value)
,{< Category = {1}, Type = {'B'} >} Sum(Value)
,{< Category = {1}, Type = {'B'} >} Sum(Value)
, Above(Column(1),1) * 1000000/{< Category = {1}, Type = {'A'} >} Sum(Value)
)

2) Measure 2 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {2}, Type = {'A'} >} Sum(Value)
,{< Category = {2}, Type = {'B'} >} Sum(Value)
,{< Category = {2}, Type = {'B'} >} Sum(Value) - Column(1)
,Above(Column(2),1)*1000000/{< Category = {2}, Type = {'A'} >} Sum(Value)
)

3) Measure 3 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {3}, Type = {'A'} >} Sum(Value)
,{< Category = {3}, Type = {'B'} >} Sum(Value)
,{< Category = {3}, Type = {'B'} >} Sum(Value) - Above(Column(2),1)
,Above(Column(3),1) * 1000000 / {< Category = {3}, Type = {'A'} >} Sum(Value)
)

4) Measure 4 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {4}, Type = {'A'} >} Sum(Value)
,{< Category = {4}, Type = {'B'} >} Sum(Value)
,{< Category = {4}, Type = {'B'} >} Sum(Value) - Above(Column(3),1)
, Above(Column(4),1) * 1000000 / {< Category = {4}, Type = {'A'} >} Sum(Value)
)

5) Measure 5 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {5}, Type = {'A'} >} Sum(Value)
,{< Category = {5}, Type = {'B'} >} Sum(Value)
,{< Category = {5}, Type = {'B'} >} Sum(Value) - Above(Column(4),1)
, Above(Column(5),1) * 1000000/{< Category = {5}, Type = {'A'} >} Sum(Value)
)

Reply, thanks!

Creator III

Hi @MeehyeOh @sk88024,

Yes, its bet to use set analysis I have turned that expression into set analysis,
@MeehyeOh  you are using a Column data set,

1. Table chart dimension is

=ValueList('A','B','Monthly(B)','Average rate of A & B')

1. Table chart Measure expression

There are five measure in the chart.

1) Measure 1 expression is

Pick(
Match(
ValueList('A', 'B', 'Monthly(B)', ' ', 'Average rate of A & B', ' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month1),
ONLY({<Name={'B'}>} Month1),
ONLY({<Name={'B'}>} Month1),
((ONLY({<Name={'B'}>} Month1) * 1000000) / ONLY({<Name={'A'}>} Month1))
)

2) Measure 2 expression is

Pick(
Match(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month2),
ONLY({<Name={'B'}>} Month2),
ONLY({<Name={'B'}>} Month2) - ONLY({<Name={'B'}>} Month1),
((ONLY({<Name={'B'}>} Month2) - ONLY({<Name={'B'}>} Month1)) * 1000000) / ONLY({<Name={'A'}>} Month1)
)

3) Measure 3 expression is

Pick(
Match(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month2),
ONLY({<Name={'B'}>} Month2),
ONLY({<Name={'B'}>} Month3) - ONLY({<Name={'B'}>} Month2),
((ONLY({<Name={'B'}>} Month3) - ONLY({<Name={'B'}>} Month2)) * 1000000) / ONLY({<Name={'A'}>} Month3)
)

4) Measure 4 expression is

Pick(
Match(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month2),
ONLY({<Name={'B'}>} Month2),
ONLY({<Name={'B'}>} Month4) - ONLY({<Name={'B'}>} Month3),
((ONLY({<Name={'B'}>} Month4) - ONLY({<Name={'B'}>} Month3)) * 1000000) / ONLY({<Name={'A'}>} Month4)
)

9 Replies
Partner - Creator

Hi, @sk88024

I don't know your exact model structure, but I only tested it with the data set you attatched as a sample, it can be calculated with the formula shown in the attached image.

Dimension : =ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ')

Reply, thanks!

Partner - Master II

the calculation is easy but the display is not straight forward
@sk88024
send me - if possible - a sample qvf file and I will show you how to accomplish your requirement

I can walk on water when it freezes
Creator III

Hi @sk88024,
Have you get a solution to this?

Creator III

Hi @sk88024,

I have Attached qvf file: you can create different columns as 2nd column by changing Fields.

** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. **

Creator
Author

@TauseefKhan Thank you so much for this.

Can you please help me to put the expression you've used in here. I am neither allowed to import or export qvf file in my organization.

Creator III

Welcome 😊

Table_1:
Load *
Inline [
Name, Month1, Month2, Month3, Month4, Month5,
A, '1864.748611', '1905.748611', 1906.998611, 1729.998611, 1695.998611
B, '6.51791423', '13.07734798', 19.1, 25.9356, 32.5306

];

Row level calculation in a table:

 A B Monthly(B) Average rate of A & B

=ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ')

Column_1:
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'A',
ONLY({<Name={'A'}>} Month1),
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'B',
ONLY({<Name={'B'}>} Month1),
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'Monthly(B)',
ONLY({<Name={'B'}>} Month1),
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'Average rate of A & B',
((ONLY({<Name={'B'}>} Month1) * 1000000) / ONLY({<Name={'A'}>} Month1)),
NULL()
)
)
)
)

Column_2:
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'A',
ONLY({<Name={'A'}>} Month2),
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'B',
ONLY({<Name={'B'}>} Month2),
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'Monthly(B)',
ONLY({<Name={'B'}>} Month2) - ONLY({<Name={'B'}>} Month1),
IF(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' ') = 'Average rate of A & B',
((ONLY({<Name={'B'}>} Month1) - ONLY({<Name={'B'}>} Month2)) * 1000000) / ONLY({<Name={'A'}>} Month2),
NULL()
)
)
)
)

Repeat for Column_2 to Column_5 by changing columns names.

Disable all sort setting:

** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. **

Partner - Creator

I think it's better to use "Set analysis" than using IF statements.

1. My Test dataset is

Test:
Load * Inline [
Type, Category, Value
A, 1, 1864.748611
A, 2, 1905.748611
A, 3, 1906.998611
A, 4, 1729.998611
A, 5, 1695.998611
B, 1, 6.51791423
B, 2, 13.07734798
B, 3, 19.1
B, 4, 25.9356
B, 5, 32.5306];

2. Table chart dimension is

=ValueList('A','B','Monthly(B)','Average rate of A & B')

3. Table chart Measure expression

: Simply use the "Above" function representing the previous row and the "Column" function specifying the column location. There are five measure in the chart.

1) Measure 1 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {1}, Type = {'A'} >} Sum(Value)
,{< Category = {1}, Type = {'B'} >} Sum(Value)
,{< Category = {1}, Type = {'B'} >} Sum(Value)
, Above(Column(1),1) * 1000000/{< Category = {1}, Type = {'A'} >} Sum(Value)
)

2) Measure 2 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {2}, Type = {'A'} >} Sum(Value)
,{< Category = {2}, Type = {'B'} >} Sum(Value)
,{< Category = {2}, Type = {'B'} >} Sum(Value) - Column(1)
,Above(Column(2),1)*1000000/{< Category = {2}, Type = {'A'} >} Sum(Value)
)

3) Measure 3 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {3}, Type = {'A'} >} Sum(Value)
,{< Category = {3}, Type = {'B'} >} Sum(Value)
,{< Category = {3}, Type = {'B'} >} Sum(Value) - Above(Column(2),1)
,Above(Column(3),1) * 1000000 / {< Category = {3}, Type = {'A'} >} Sum(Value)
)

4) Measure 4 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {4}, Type = {'A'} >} Sum(Value)
,{< Category = {4}, Type = {'B'} >} Sum(Value)
,{< Category = {4}, Type = {'B'} >} Sum(Value) - Above(Column(3),1)
, Above(Column(4),1) * 1000000 / {< Category = {4}, Type = {'A'} >} Sum(Value)
)

5) Measure 5 expression is

=Pick(Match(ValueList('A','B','Monthly(B)','Average rate of A & B'),'A','B','Monthly(B)','Average rate of A & B')
,{< Category = {5}, Type = {'A'} >} Sum(Value)
,{< Category = {5}, Type = {'B'} >} Sum(Value)
,{< Category = {5}, Type = {'B'} >} Sum(Value) - Above(Column(4),1)
, Above(Column(5),1) * 1000000/{< Category = {5}, Type = {'A'} >} Sum(Value)
)

Reply, thanks!

Creator III

Hi @MeehyeOh @sk88024,

Yes, its bet to use set analysis I have turned that expression into set analysis,
@MeehyeOh  you are using a Column data set,

1. Table chart dimension is

=ValueList('A','B','Monthly(B)','Average rate of A & B')

1. Table chart Measure expression

There are five measure in the chart.

1) Measure 1 expression is

Pick(
Match(
ValueList('A', 'B', 'Monthly(B)', ' ', 'Average rate of A & B', ' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month1),
ONLY({<Name={'B'}>} Month1),
ONLY({<Name={'B'}>} Month1),
((ONLY({<Name={'B'}>} Month1) * 1000000) / ONLY({<Name={'A'}>} Month1))
)

2) Measure 2 expression is

Pick(
Match(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month2),
ONLY({<Name={'B'}>} Month2),
ONLY({<Name={'B'}>} Month2) - ONLY({<Name={'B'}>} Month1),
((ONLY({<Name={'B'}>} Month2) - ONLY({<Name={'B'}>} Month1)) * 1000000) / ONLY({<Name={'A'}>} Month1)
)

3) Measure 3 expression is

Pick(
Match(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month2),
ONLY({<Name={'B'}>} Month2),
ONLY({<Name={'B'}>} Month3) - ONLY({<Name={'B'}>} Month2),
((ONLY({<Name={'B'}>} Month3) - ONLY({<Name={'B'}>} Month2)) * 1000000) / ONLY({<Name={'A'}>} Month3)
)

4) Measure 4 expression is

Pick(
Match(ValueList('A','B','Monthly(B)',' ','Average rate of A & B',' '),
'A', 'B', 'Monthly(B)', 'Average rate of A & B'
),
ONLY({<Name={'A'}>} Month2),
ONLY({<Name={'B'}>} Month2),
ONLY({<Name={'B'}>} Month4) - ONLY({<Name={'B'}>} Month3),
((ONLY({<Name={'B'}>} Month4) - ONLY({<Name={'B'}>} Month3)) * 1000000) / ONLY({<Name={'A'}>} Month4)
)

Creator
Author

@TauseefKhan @MeehyeOh Thank you so much for your help on this. 🙂 Really Appreciated.

Thanks again!