3 Replies Latest reply on Dec 22, 2016 6:24 AM by Jonathan Drummey

# Display Columns from a single worksheet to two worksheets

Hello,

I basically have got pie-charts for 15 different cars on a single worksheet. Now I want pie-chart for 8 cars to be displayed on 1st worksheet and for the the rest of the cars on 2nd worksheet.

I created a calculated field "rowFilter" with the formula as:

IF [size] % 2 = 1

THEN [index] <= ([size] / 2) + 1

ELSE [index] <= ([size] / 2)

END

"size" and "index" are another measures which have SIZE() and INDEX() functions in them.

The definition for size is: SIZE()

The definition for index is: INDEX()

After this I have set "rowFilter" to TRUE for one worksheet and FALSE for another worksheet.

But for me the data gets displayed in a weird manner. I get 8 pie-charts on both the worksheets but a pie-chart repeated for a car on 2nd worksheet. It basically displays correct data for 14 cars on both the worksheets but it displays half data for 1 car on 1st worksheet and remaining half data for that same car on 2nd worksheet.

I am not getting this why this is happening and not able to identify this issue.

I the two worksheets, attached below you can see that the pie-chart for "Hilux" car is repeated on both worksheets. It displays half data for this car on first worksheet and the rest on the 2nd worksheet.

I cannot attach my worksheet as the data is confidential but I can attach screenshots for my two worksheets.

I will be great if anyone can help me with this issue.

• ###### 1. Re: Display Columns from a single worksheet to two worksheets

It's easy.

Create Index calculation: Index()

For first sheet drag in filkter and select 1 to 8

for second select the others

• ###### 2. Re: Display Columns from a single worksheet to two worksheets

Hello Luciano,

I have removed my rowFilter.

Have added index calculated field in filter containing calculation: INDEX()

But it doesn't work right. I have selected index values in a range from 1 to 7 for Pichart1 worksheet. The screenshot is:

Similarly I selected values from 8 to 15 in next worksheet. The snapshot is:

It doesn't give right pie-charts.

• ###### 3. Re: Display Columns from a single worksheet to two worksheets

INDEX() computes a value for each distinct address. With 15 cars and 1 or 2 values of the CCFC auto population dimension that's going to be 15+N values returned for INDEX(), the exact values being dependent on the addressing of the calculation. That's why some pies are repeating.

A better calculation would be RANK_DENSE(MIN([Car Name])) with a compute using on both the Car Name and CCFC auto population dimensions, that will return a number from 1-N for each Car Name regardless of how many CCFC auto population values there are for that car name.

Jonathan