1 2 Previous Next 18 Replies Latest reply on Jun 26, 2014 4:15 AM by Suhrid Ghosh

Getting count of subscriber for single row

Hi ,

How can i get the count of customer id's which is displayed for a single row as shown in the below picture.

Say here in this case for Group 2-3 times in a week , Week - 18 the customer id is 1 and 2 , so for this i need the count as 2.

Similarly for Group Once in a month , Week -18 the customer id is 4 , so for this i need the count as 1. Similarly for Group Once in a week the count will be 1.

Attaching the sampled workbook, have found any workaround on this , please suggest ...

• 1. Re: Getting count of subscriber for single row

Anybody any clue on this ???

• 2. Re: Getting count of subscriber for single row

hey Matt Lutton , if you have time can you have a look at this , tried a lot but not getting any clue .... Thanks

• 3. Re: Getting count of subscriber for single row

I did have a look, but was confused.  The "Group" calculation is dependent on TransactionID, which isn't in the view.  Pulling it into the view changes the granularity of the view.  So, I'm not sure what you are looking to accomplish, exactly, and I'm not sure if the original view was set up as you intended it to be.

• 4. Re: Getting count of subscriber for single row

That view is depicting what all customers are doing transactions once a week , once a moth and 2-3 times a week. so that group calculation is done accordingly to find the customer which fall in to the particular group , this calculation is dependent on week of date.

But here you can see that i am able to get customer id not the count of customers , i want to do a pie chart for group for the % of customers which fall on to the particular group

• 5. Re: Getting count of subscriber for single row

Can you relate with this view now ??

Transaction id is not unique here ... we need to take distinct

• 6. Re: Getting count of subscriber for single row

No, unfortunately, I'm just plain confused on this one.

• 7. Re: Getting count of subscriber for single row

Are you confused on to the group calculation , how its working ???

• 8. Re: Getting count of subscriber for single row

Group is a Table Calculation, and we can't partition Table Calculations (like a WINDOW_COUNT of COUNTD(ID), or similar) on the results of another Table Calc, so that's a problem here.  But yes, I don't really understand the Group calculation completely.

See this post for more details: Partitioning by Table Calculations | Drawing with Numbers

If he has the time, I'm sure Jonathan Drummey can provide some clarity here.

• 9. Re: Getting count of subscriber for single row

Hey Suhrid,

If you are taking week in worksheet then how you are categorizing it in different levels? How you can show Once in a month in front of weeks? If you are showing weeks then for a particular week one ID may be come once but for another week it may come 2-3 times. So, how you are categorizing it? I am also confused here & waiting for response from Mr. Jonathan Drummey.

Warm Regards,

Prashant Sharma - India | LinkedIn

• 10. Re: Getting count of subscriber for single row

The Grouping calculation for # of times per month only makes sense if the data source contains a single month of data, which is true for the sample data. However, is that true for the real data? If not, then both the Month and Week will need to be used to appropriately partition the view to generate the once a month vs. once a week/2+ times per week.

I’d need that question answered before being able to come up with an exact solution. Also, what are your production data volumes (particularly the # of Consumer IDs and # of Weeks being displayed), and your data source?

Tableau doesn’t have a built-in way to partition table calculations on other table calculations, and although we can hack it (such as using LOOKUP/PREVIOUS_VALUE and RANK calculations) that gets difficult. My thinking is to make the Grouping calc a discrete regular aggregate by using a self-data blend, or to make it a dimension by using a combination of Sets. And, of course there’s always a custom query/view/Custom SQL to pre-aggregate the data.

Jonathan

• 11. Re: Getting count of subscriber for single row

Hello Prashant,

In the workbook i uploaded , i agree the week are displayed but that is not the final visualization. The week header has to be hidden and one calculation field that is first() ==0 has to be kept in filter shelf and set to true and the calculation to be  acted upon transaction date. This way you will get only three rows like

Once in a Month                 4

Once in a week                  3

2-3 times in a week            1,2

thanks,

Suhrid Ghosh

• 12. Re: Getting count of subscriber for single row

Well, this is as close as I can get it. There's lots of workarounds, hiding of rows and headers, as well as manipulating formulas to get the values....but the look is spot on

Maybe someone can come up with a better solution for the Calculation1 measure that mimics the 1,2.

Good luck.

Don

• 13. Re: Getting count of subscriber for single row

HI Jonathan,

Thanks for looking in to this. The final goal of mine here is to show a pie chart which has three slices . Each slice will depict a group i.e Once a month , Once a week and 2-3 times in a week  and the size of the slice will depict the % of customer count who fall on to the category.

Currently in my visualization you can see week but that is not the final one . The final visualization i have replied to Prashant how it will look like. But in that i was able to achieve only the customer ids which fall on to that category. I had only 3 weeks of data for that , but in actual it can be up to 1-2 years data and # of customers cant say approx.

I still have to look on to the blog which Matthew has mentioned regarding partitioning table calculations.

Thanks,

Suhrid Ghosh

• 14. Re: Getting count of subscriber for single row

Hi Don,

Thanks for the solution , will have a look when i reach tomorrow morning to office ... Its pretty late night here

n football is up

Thanks,

Suhrid Ghosh

1 2 Previous Next