Error in Creating Dimension for Pie Chart

[jagdishbr]

Hi,

I am working on creating a dimension for a pie chart with the categories 'Top 10 Covered,' 'Rest Covered,' and 'Uncovered.'

The criteria are as follows:

1. Top 10 Covered = period='FY23', profit>0, sum(sales) using rank function 

2. Rest Covered = period='FY23', profit>0, sum(sales) excluding top 10 values

3. Un Covered = period='FY23', profit=0, sum(sales) 

I tried below set expression and end-up as "invalid Dimension"

If(
Rank(Sum({<Period={'FY23'}, profit={'>0'}>} sales), 1, 1) <= 10,
'Top 10 Covered',
If(Sum({<Period={'FY23'}, profit={'>0'}>} sales) > 0,
'Rest Covered',
If(Sum({<Period={'FY23'}, profit={0}}>} sales) >= 0,
'Un covered',
Null()
)
)
)

Am i doing something wrong? Please guide

[anat]

You logic should be like below

If rank<=10 then 'top 10', 

If rank >10 and sum(values) not equal 0 then 'rest covered'

else 'unknown'

[Padma123]

Try This...

load *,If(AutoNumber(sales)<=10 and period='FY23' and profit>0,'Top 10 Covered',
if(period='FY23' and profit=0,'uncoverd','Remaining')) as result;

load * Resident table1
order by sales;

In Pie chart

dim-result

measure-sum(sales).

[jagdishbr · Accepted solution]

Thanks Anat and Padma123 for your time and effort in addressing the issue. however, i figured out the solution.

={<period={'FY23'}>}aggr(IF(RANK(sum({<profit={">0"}>}Sales),4)<=$(vSliderValue),'Top $(vSliderValue) Covered',
IF(RANK(sum({<profit={">0"}>}sales),4)>$(vSliderValue),'Rest Covered', 'Un-covered')),client_name)