Row level calculation in a table

[sk88024]

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

[MeehyeOh]

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!

[ali_hijazi]

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

[TauseefKhan]

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

[TauseefKhan]

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. **

[sk88024]

@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.

[TauseefKhan]

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. **

[MeehyeOh · Accepted solution]

Hi, @TauseefKhan @sk88024 

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!

[TauseefKhan · Accepted solution]

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)
)

[sk88024]

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

Thanks again!