1

This is the data table that I have created

enter image description here

I need to sort the data by 'April2017' in descending order and then select the top 10 projects.

When we select top10 basis April2017, the output should be

Desired Output

Instead what I get isenter image description here

Here is what I've tried out so far,

  1. Created a calculated field Calculation1 = iif([Year_Month]=MAKEDATE(2017,4,1),[Claim Count],0)
  2. Sorted Projects based on 'Calculation1'
  3. Drag Project to Filter and select Top10 based on sum([Calculation1])

I am unable to understand how the top10 here is being derived.

Where am I going wrong?

The chart that I am trying to get should be similar to enter image description here

Please help me with this problem.

darkage
  • 857
  • 3
  • 12
  • 22

2 Answers2

1

You can filter to a selected portion of data in a calculation and use as desired. So create a calculated field called, say April_2017_Foobars, defined as:

if datetrunc('month', [Year_Month]) = #04/01/2017# then [Foobars] end

This field return [Foobars] for the April 2017 rows and null for other rows. Nulls are ignored by aggregate functions, so if you aggregate with SUM() or AVG() etc, the effect is to filter to April 2017 for that field alone.

Then you can use April_2017_Foobars for sorting and defining top filters for your Project field. This is a very general technique that is useful in all kinds of situations.

You can generalize it a bit to use a parameter for the special month rather than hard code it - or use an LOD calc to find the last month in your dataset if you always intend to use the latest month.

P.S. You can use the makedate() function instead of a date literal if you prefer and your data source supports that function. Might avoid any confusion about date literal formats being different in various countries.

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • how do I create the field [Foobars] ? Is there a way I can send you my workbook so that you can take a look and guide me accordingly? – darkage May 10 '17 at 16:31
  • I used Foobars to represent whatever field you have on the Text shelf in the view you posted. If you are counting records, use [Number of Records], if you are counting Sales, use [Sales] etc – Alex Blakemore May 10 '17 at 18:46
  • I tried out your method, Created a calculated field: iif([Year_Month]=MAKEDATE(2017,4,1),[Claim Count],0) Sorted Projects by this field. Till here everything is fine. When I put projects in the filter and select top 10 based on this calculated field, I do not get the intended output. I have edited the question with the updated problem. – darkage May 11 '17 at 04:38
  • I have figured out the solution. The trick is to understand the order in which different filters take effect. – darkage May 11 '17 at 06:36
1
  1. create Calculation1 field: iif([Year_Month]="April 2017",[Number],0)
  2. sort Project in descending order on Calculation1 Sum
  3. drag Project to filters, and do Top > By Field > Top: 10 by Calculation1 Sum
JJBee
  • 56
  • 2
  • 8
  • I have edited the question with the latest update. The flow works fine till the sorting part but breaks up when we introduce the filter. Please provide your suggestion – darkage May 11 '17 at 04:51
  • I have figured out the solution. The trick is to understand the order in which different filters take effect. – darkage May 11 '17 at 06:35
  • 1
    The idea of the calculated field did help. Additionally I had to figure out the order in which the filters work and set all filters but "Top N" to be context filters. – darkage May 12 '17 at 04:31