Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the below table:
Company | Amount |
---|---|
A | 100 |
B | 200 |
C | 90 |
D | 85 |
What I want to do is to compare A to other companies:
Company | Amount | Comparison |
---|---|---|
A | 100 | No Comparison Necessary |
B | 200 | B is greater than A |
C | 90 | C is lower than A |
D | 85 | D is lower than A |
I just wanted to see if there is an easy way to do this with or without creating additional dimensions/tables in the load script.
Straight or pivot table solutions are highly desirable.
Thank you.
Front end solution:
Dimension:
Company
Amount
Expression:
=If(Company = 'A', 'No Comparison Necessary',
If(Only(TOTAL {<Company = {A}>}Amount) > Amount, Company & ' is greater than A',
If(Only(TOTAL {<Company = {A}>}Amount) < Amount, Company & ' is less than A')))
This may be not what you want, because I am sure the data you provided above must be a sample, but this might give you an idea to proceed.
Table:
LOAD Company,
Amount
FROM
[https://community.qlik.com/thread/172514]
(html, codepage is 1252, embedded labels, table is @1);
Temp:
LOAD FirstValue(Amount) as Temp
Resident Table;
LET vTemp = Peek('Temp');
Join (Table)
LOAD Company,
If(Company = 'A', 'No Comparison Necessary',
If(Amount > $(vTemp), Company & ' is greater than A',
If(Amount < $(vTemp), Company & ' is lower than A'))) as Comparison
Resident Table;
DROP Table Temp;
Just saw your comment that you need this on the front end.
Hi,
You can you PEEK() function in the script to compare fields.
Peek(fieldname [, row [, tablename ]] )
i.e. IF(Amount= Peek('Amount'), 1) AS %FieldCompareFlag
You can use this flag in your straight table and hide it
Hope this helps
in the front end you can try like this:
if(Company='A','No Change', if(Amount<=$(vAmount),Company&' is Lower than A', Company&' is Greater than A'))
NOTE: create variable vAmount=num(only({<Company={'A'}>}Amount))
Front end solution:
Dimension:
Company
Amount
Expression:
=If(Company = 'A', 'No Comparison Necessary',
If(Only(TOTAL {<Company = {A}>}Amount) > Amount, Company & ' is greater than A',
If(Only(TOTAL {<Company = {A}>}Amount) < Amount, Company & ' is less than A')))
You are the man, Sunny.
Thank you so much.
I may have a follow up question later on though.
I know Robert Mika posted a solution and I don't know what happened to it. It disappeared.
Thank you again.
Not sure either, but if you need to see his response, this was it:
if(rowNO()=1,'no comaprision',if(rangesum(above(sum(Amount),1,1))<SUM(Amount),'lower than A','Greater than A'))
Thank you again.
Not a problem
I am glad I proved helpful.