Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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)
)
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!
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
Hi @sk88024,
Have you get a solution to this?
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. **
@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.
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. **
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!
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)
)
@TauseefKhan @MeehyeOh Thank you so much for your help on this. 🙂 Really Appreciated.
Thanks again!