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

    [Number of Records]/customer as a constant

    Elina Jeskanen

      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
          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

            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
              Elina Jeskanen

              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

                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
                  Elina Jeskanen

                  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

                    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 -

                    Capture.JPG

                    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]

                    Capture.JPG

                    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,

                    Prashant Sharma - India | LinkedIn

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

                      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?:

                       

                      freq.png