topic Re: Row level calculation in a table in App Development

Row level calculation in a table

sk88024 — Wed, 22 May 2024 22:10:29 GMT

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,

Re: Row level calculation in a table

MeehyeOh — Thu, 23 May 2024 04:22:52 GMT

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!

Re: Row level calculation in a table

ali_hijazi — Thu, 23 May 2024 08:14:25 GMT

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

Re: Row level calculation in a table

TauseefKhan — Thu, 23 May 2024 08:19:17 GMT

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

Re: Row level calculation in a table

TauseefKhan — Thu, 23 May 2024 10:21:23 GMT

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

Re: Row level calculation in a table

sk88024 — Thu, 23 May 2024 11:19:59 GMT

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

Re: Row level calculation in a table

TauseefKhan — Thu, 23 May 2024 11:54:39 GMT

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:

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

Re: Row level calculation in a table

MeehyeOh — Thu, 23 May 2024 23:34:56 GMT

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!

Re: Row level calculation in a table

TauseefKhan — Fri, 24 May 2024 06:56:39 GMT

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

Re: Row level calculation in a table

sk88024 — Fri, 24 May 2024 10:40:52 GMT

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

Thanks again!