5 Replies Latest reply on Dec 1, 2017 10:07 AM by Simon Runc

# Difference between Number of days shown separately by number of orders(All orders are unique by EmailAddress)

Thank you so much for this great platform for the beginners, as whenever you are stuck there is someone to help you out. I have two questions where i am stuck.

1. In sales sheet of my workbook everything looks good but I want to sum up everything like this in a separate sheet which I am not able to do :

No. Of Orders            sum of rd_numorders           sum of rd_ordered

2                                    16                                        2342

3                                     32                                       4563

4+                                   56                                       5467

so here rd is reference date(means for eg : 11/27/2016), I want to know all customers who in their entire history purchased 2 times how many orders we got from them on this rd and same with sum of rd_ordered.(This is just example so this will not match with workbook).

2. This is too complex for me. What I am trying to get is on second sheet of workbook is their anyway that i can see median difference between most recent purchase and 2nd last purchase by no. of orders shown separately?? Like i mean on a single sheet can I see differently for those who placed order 2 times whats the median difference of day between their most recent and 2nd last order, then for the 5,8.....order...on the same sheet, so that i don't have to change filter every time.

Any help is appreciated. As I am completely stuck. For reference I am attaching sample workbook.

• ###### 1. Re: Difference between Number of days shown separately by number of orders(All orders are unique by EmailAddress)

hi Simran,

So I think you are pretty much there with this! Your LoD to return (as a dimension) to each customer their order count is how I'd have done it.

All I've done is add the following calculation to create your groups

[No of Orders - Grouped]

IIF([no of orders]>=4, '4+',STR([no of orders]))

and then just use this as a dimension. I've created the table in the "Summary - SR" tab.

You're median calculation also looks good (and we can just add in the No of Orders - Grouped. To verify that your calculation is working I've created "Median - Check - SR" and "Median - Check - ByCohort - SR", where I've detailed out the Last/2nd-From-Last Difference by Email (customer) and then used the Reference Line to show the Median, which as you'll see matches your one (and also the one where I've brought the grouped dimension in.

Let me know if anything doesn't make sense, or doesn't solve your issue

• ###### 2. Re: Difference between Number of days shown separately by number of orders(All orders are unique by EmailAddress)

Thankyou so much Simon Runc It worked but now I am having another problem. I am trying to do total ordered but it is giving me error cannot turn on grand totals for discrete measures.

Formula used to calculate cm_no. of orders : {FIXED[lower email addrress],[Orderdate]:COUNTD([Orderid])}

Regards,

Simran

• ###### 3. Re: Difference between Number of days shown separately by number of orders(All orders are unique by EmailAddress)

Yes you can't get grand totals on discrete pills (even if they are a discrete measure). If you set up the sheet like this you can have the GTs

1 of 1 people found this helpful
• ###### 4. Re: Difference between Number of days shown separately by number of orders(All orders are unique by EmailAddress)

yeah got it. But can u please explain me why it worked like this? Or any link where I can understand the concept?

• ###### 5. Re: Difference between Number of days shown separately by number of orders(All orders are unique by EmailAddress)

This is one of the best articles I've ever read on the subject of blue and green pills! and should help

https://www.theinformationlab.co.uk/2011/09/23/blue-things-and-green-things/

It gets a bit conceptual...but in short Discrete pills (even if they are measures) create a distinct list of values, depending on the level of detail in the Viz. If you look at the below example, my discrete SUM of Sale pill creates 12 rows, not the 1 you might expect, as it has to create a row (header) for every Month/Segment combination. It's the same a string dimension does, just (conceptually) a bit trickier when the distinct values are numbers!

As such it can't compute a total of these discrete pills, as it can when they are in the measures pane. It's part of the principle of blue and green pills in the was Tableau was set up, but they thought of this and this is why it (automatically) creates the measure names/values to get round it.

Hope that makes a bit of sense, but definitely give that article a read (after reading that I had a "Oh, I get it moment" within Tableau, and never looked back!)