10 Replies Latest reply on Apr 20, 2017 10:11 AM by Justin Larson

Count No of Records within each Partition

mike.dale Apr 19, 2017 1:56 PM

Hi All,

I am trying to calculate the number of records within each partition. I thought it would be a simple LOD calculation however for some reason I just can't get this to work.

My table looks something like this:

CarId	Purchase_Date
C123	01/02/2017
	04/02/2017
	02/03/2017
	01/03/2017
	04/03/2017
	04/02/2017
	04/02/2017
C323	02/03/2017
C432	04/03/2017
	02/03/2017
	10/03/2017
	12/03/2017
	14/03/2017
C453	04/02/2017
	12/03/2017
	04/03/2017

The LOD I put together looks like this: {INCLUDE [CarId],[Purchase_Date]: COUNTD([Purchase_Date])} . But this isn't working. I need it to return the number of rows in each partition e.g.:

C123 = 7

C323 = 1

C4342 = 6

C453 = 3

Then I need to use this as a filter and exclude values below 5. So CarId C323 and C453 should be filtered out as they have less than 5 records each.

Would much appreciate any help.

Hope it make sense.

Thanks

1. Re: Count No of Records within each Partition

Deepak Rai Apr 19, 2017 2:28 PM (in response to mike.dale)
Hi Mike,
Check the screenshots:
After Removing >5

LOD for Count.twbx 27.7 KB
Like Show 0 Likes(0)

Actions
2. Re: Count No of Records within each Partition

Deepak Rai Apr 19, 2017 2:35 PM (in response to mike.dale)

Oh, you need this after filtering: Change your Filter to FALSE.
Like Show 0 Likes(0)

Actions
3. Re: Count No of Records within each Partition

mike.dale Apr 19, 2017 3:15 PM (in response to Deepak Rai)

hmm...thanks but it didnt quite work.

I should have mentioned that the table above is a derived view which I derived by using other filters and calculations.

I followed your example but used INCLUDE instead of Fixed. It returned the number of rows in the datasource (xls) for each date. For example where C232 has only 1 record on the view, it would display 13, because there are 13 records with that ID and date in the xls.
Like Show 0 Likes(0)

Actions
4. Re: Count No of Records within each Partition

Deepak Rai Apr 19, 2017 3:27 PM (in response to mike.dale)

Hi Mike,
so you need to provide original data and also instructions clearly what needs to be done,It was not in your Post. If you can do that, it would be easy to help.
Thanks
Deepak
Like Show 0 Likes(0)

Actions
5. Re: Count No of Records within each Partition

mike.dale Apr 19, 2017 4:20 PM (in response to Deepak Rai)
Please see sheet 5 in attached workbook. The number of records are wrong.

LOD for Count_Mike.zip 105.8 KB
Like Show 0 Likes(0)

Actions
6. Re: Count No of Records within each Partition

Deepak Rai Apr 19, 2017 4:29 PM (in response to mike.dale)

Hi Mike,
Somehow I cant open your twb file. pl attach twbx.
Thanks
Deepak
Like Show 0 Likes(0)

Actions
7. Re: Count No of Records within each Partition

mike.dale Apr 19, 2017 5:02 PM (in response to Deepak Rai)
Sorry, please try now.

Let me know if you still having problems opening it or if you have any other questions.

Thanks.

LOD for Count.twbx 131.5 KB
Like Show 0 Likes(0)

Actions
8. Re: Count No of Records within each Partition

Daniel Vincent Apr 19, 2017 8:38 PM (in response to mike.dale)

Make sure you let us know what version you're uploading. Not everyone is using the newest version.

Based on your workbook is this what you expect to see for the results?
WINDOW_SUM(COUNTD([Car Id]))
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
9. Re: Count No of Records within each Partition

Deepak Rai Apr 19, 2017 10:17 PM (in response to mike.dale)
Hi Mike,
So continuing from the LOD and using the following Calculations, you are able to get final picture as :

The Calculations are :
This would Return 1 for all Rows and then Summing up using:
Then for 2nd Part of your Question for Retaining Totals>5

Drag the Calculation 3 to Filter and Select TRUE to get:
Please See attached.
Thanks
Deepak

LOD for Count.twbx 135.5 KB
Like Show 0 Likes(0)

Actions
10. Re: Count No of Records within each Partition

Justin Larson Apr 20, 2017 10:11 AM (in response to mike.dale)

Sets may be a better option here. Right click on CardId field and choose Create>Set:

Drop resulting set into filter.
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions