7 Replies Latest reply on Dec 8, 2014 3:30 AM by Elina Jeskanen

# [Number of Records]/customer as a constant

I would want to create a constant of the number of records per customer, so that I could visualize how many customers have 1 order, 2 orders, 3 orders and so on and have age and *** in the column and row to see the affect of these variables on how many 1, 2, 3 order placers we have.

I've tried to create a calculated field: str(COUNT([unique.identifier])/[Number of Records]).

How ever in the visualization Tableau is turning the calculated field into AGG and summing them up (1+2+3+...), so that I can only see total number of orders per age and ***.

How could I make a constant (or parameter) of the number of orders per customer?

• ###### 1. Re: [Number of Records]/customer as a constant

To clarify: I would like to see for  example:

How many 20 year old male subscribers we have who have placed 5 orders?

• ###### 2. Re: [Number of Records]/customer as a constant

Hi,

Create a calculated field in which break your age according to your requirement like if you want to create age group like 0-25, 25-50 etc then create a calculated field like below-

IF [AGE]>=0 and [AGE]<=25 then '0-25' elseif AGE]>26 and [AGE]<=50 then '26-50' else '>50' end

Put this calculated field into your worksheet & put your customer name there & convert it to count distinct measure. You can also create a calculated field with countd(Customer) & use it in sheet.

Let us know if you have further query. If this will not help then try to attach a sample workbook (.twbx) so that we can suggest you according to what you have in datasource.

Warm Regards,

Prashant Sharma - India | LinkedIn

1 of 1 people found this helpful
• ###### 3. Re: [Number of Records]/customer as a constant

Thanks, but I'm afraid those do not help.

I would like to have number of customers in age group who have ordered 1, 2,3, 4, 5 ... times. Now the data is in form:

customerID1 ... order_date1  ... male ... 20

customerID1 ... order_date2 ... male ... 20

customerID2 ... order_date3  ...felmale ...25

customerID3 ... order_date3  ...male ...20

And I would like to have:

***      age      no of orders      no of cases

female   25     1                         1

male      20     1                         1

male      20     2                         1

• ###### 4. Re: [Number of Records]/customer as a constant

Yes that is why I told you that every thing is depends upon what you have & what you want to show. Check the attached workbook & here I am using count() & countd().

One Suggestion from my side. Create the range of age not exact age ike what I have used in previous reply.

arm Regards,

Prashant Sharma - India | LinkedIn

• ###### 5. Re: [Number of Records]/customer as a constant

I'm sorry I was not clear enough.

Your suggestion was close (thank you very much for it!), but gives the _total_ orders and _total_ cases per ***/age group and the number of orders is still missing:

***      age      no of orders     no of cases      no of total orders

female   25     1                         3                         3

male      20     1                         21                        21

male      20     2                         11                         22

male     20     3                             5                         15

• ###### 6. Re: [Number of Records]/customer as a constant

In previous thread you do not want total but now you are showing that you want total also. Now, I am confused. In first reply you show me one sample data -

customerID1 ... order_date1  ... male ... 20

customerID1 ... order_date2 ... male ... 20

customerID2 ... order_date3  ...felmale ...25

customerID3 ... order_date3  ...male ...20

then you want to create a report like following -

***      age      no of orders      no of cases

female   25     1                         1

male      20     1                         1

male      20     2                         1

I have created the same with same datasource in attached file -

In second reply you are showing another output -

***      age      no of orders     no of cases      no of total orders

female   25     1                         3                         3

male      20     1                         21                        21

male      20     2                         11                         22

male     20     3                             5                         15

Here, Can you please define "Number of Orders" & "Number of Cases" in this case. Do you want Number of Orders to be break? (means you want to count the number of cases where only 1 order is placed & where 2 order is placed & so on?). If you want something like that then first convert Agg(Number of Order) into dimension & create another calculated field with formula -

[Number of Orders]*[Number of Cases]

Also, check the attached file & let me know what exactly you want. If this will not work then create one excel with same data & also put the output you want.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 7. Re: [Number of Records]/customer as a constant

This was very difficult to explain. Thank You very much for your effort!

I finally found a way to get what I needed:

I made a copy of the data with all the orders and took sums of orders per customer (pot_sum) and joined (or do you call it blend) it with the original datasource (aineisto). So now for example I can see that there were 118 18 year old females who ordered once and 76 who ordered two times and so on. So in this table the total amount of

• 18 year old females are 118 + 76 + 62+ ...
• orders by 18 year old females are 118*1 + 76*2+ 62*3 +...

Is it possible to do this kind of frequency table (below) without joining the table to it self?: