Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Masters,
I have a table with current years sales and previous years sales crossing companies.
How can I build up a bar chart to show the CY vs PY variance rate but only to display the top 6 companies with highest CY sales?
you could create dimension something like this
if(aggr(rank(sum(LineSalesAmount)),Company)<=6,Company) exclude null rank and aggr combination to get top 6 companies on sale. replace the sum() with your CY expression
It should be below
Dimension:
// Check option suppress null values
=aggr(only({<Company ={"=rank(sum([CY Sales]))<7"}>}Company),Company)
Expression:
sum([CY Sales])/sum([PY Sales])-1
Assuming below CY & PY expression
CY: sum({<Year={$(=max(Year))}>}Sales)
PY : sum({<Year={$(=max(Year)-1)}>}Sales)
Create a bar chart
Dimension:
// Check option suppress null values
=aggr(only({<Company ={"=rank(sum({<Year={$(=max(Year))}>}Sales))<7"}>}Company),Company)
Expression:
(sum({<Year={$(=max(Year))}>}Sales)-sum({<Year={$(=max(Year)-1)}>}Sales))/
sum({<Year={$(=max(Year))}>}Sales)
you could create dimension something like this
if(aggr(rank(sum(LineSalesAmount)),Company)<=6,Company) exclude null rank and aggr combination to get top 6 companies on sale. replace the sum() with your CY expression
Hi Kush,
Allow me to spell out the exact fields I have:
Company | CY Sales | PY Sales |
A | xxx | xxx |
B | xxx | xxx |
C | xxx | xxx |
D | xxx | xxx |
… | … | … |
I guess no need for max (year)
I put measure/width expression with "Sum CY/ Sum PY -1 "; I guess it is pretty much the same logic as you provided.
Would you please tweak a bit on the dimension based on the fields at this table? I tried your provided expression, but did not work...I believe I missed sth....maybe these clear fields may help me better tease out
Thanks
It should be below
Dimension:
// Check option suppress null values
=aggr(only({<Company ={"=rank(sum([CY Sales]))<7"}>}Company),Company)
Expression:
sum([CY Sales])/sum([PY Sales])-1
Hi Dilipranjith,
It works. But why it does not work on KPI when I put into label...I thought it would return the top 6 companies ' name if it worked for demension.
Hi Kush,
Thanks for your further clear version. It works!
one more question, as the dimension, I tried to paste it into KPI label, assuming it would return top 6 companies.
But it does not ....anything I miss?