-
1. Re: [Number of Records]/customer as a constant
Elina Jeskanen Dec 7, 2014 10:42 PM (in response to Elina Jeskanen)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
Prashant Sharma Dec 7, 2014 11:13 PM (in response to Elina Jeskanen)1 of 1 people found this helpfulHi,
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,
-
3. Re: [Number of Records]/customer as a constant
Elina Jeskanen Dec 7, 2014 11:29 PM (in response to Prashant Sharma)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
Prashant Sharma Dec 7, 2014 11:45 PM (in response to Elina Jeskanen)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,
-
Number of Orders.twbx 10.3 KB
-
-
5. Re: [Number of Records]/customer as a constant
Elina Jeskanen Dec 8, 2014 12:22 AM (in response to Prashant Sharma)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
Prashant Sharma Dec 8, 2014 1:25 AM (in response to Elina Jeskanen)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.
It is always better to ask your all requirements in a single thread.
Warm Regards,
-
Number of Orders.twbx 10.1 KB
-
-
7. Re: [Number of Records]/customer as a constant
Elina Jeskanen Dec 8, 2014 3:30 AM (in response to Prashant Sharma)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?: