Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I can't see the wood for the trees. I have a PEOPLE table with a field SALES_LAST_PERIOD. I would like an expression that provides the rank for the people in terms of their SALES_LAST_PERIOD.
When I create a table and include a "measure" column with the expression: rank(SALES_LAST_PERIOD) I always get a 1 in the table for every row.
If I try to add a "dimension" column with the same expression I get "invalid dimension"
This seems a bit obvious to me?!
Ultimately I want to use set analysis to compare a metric for a PERSON with the avergae metric for the top 25 people based on their sales.
I am being daft here but I don't know what you mean - how do I differentiate "dimensions" from "expressions"
If you create a new chart
You can see there are several tabs..
Dimension tab use PERSON_ID
and Expression tab use those three expressions..
in the chart dimension use if( aggr( Rank( Sum(SALES_LAST_PERIOD)),PERSON_ID) <=25, PERSON_ID)
in the expression - Sum(SALES_LAST_PERIOD)
here you will get top 25 sales persons
if( aggr( Rank( Avg(SALES_LAST_PERIOD)),PERSON_ID) <=5, PERSON_ID)
here you will get top 5 Avg persons
So when I create a chart I can see how this works...
If I create a table (not a chart) it acts strangely. I can add a column to my table as a "measure" and only the top 5 rows are returned but the value in the column is always 1 - not the rank!
Do one thing... Whatever QVW you have created, attach it here as sample.
OK - I think I am there.
One minor amend I want to make is that I want the selection to be the top 5 regardless of the selections I make.
I thought I could amend this expression as suggested earlier
[CALLS_LAST PERIOD] - AVG(Total {<PERSON_ID = {"=Rank(SUM(SALES_LAST_PERIOD))<=5"}>}[CALLS_LAST PERIOD])
To:
[CALLS_LAST PERIOD] - AVG(Total {<PERSON_ID = {"=Rank(SUM(1<SALES_LAST_PERIOD))<=5"}>}[CALLS_LAST PERIOD])
But this does not seem to work - how should I adjust the expression to ignore current selections?