5 Replies Latest reply on Apr 1, 2020 4:07 AM by Angel Eremiev

Graph based on different column

Hi guys,

I am having an interesting struggle. My goal is to produce a line graph, like this:

On the Х-axis I am placing a specific date, lets say, date when a customer signs up. On the Y-axis I am showing a calculation. And here comes the interesting part - I would like the calculation to be based on another column, lets say - date when first order is made. So if 270 clients register in March 2018, and 80 make deposits in the same month, I would like the value for March 2018 to be 80/270. The tricky part is that the X-axis shows the sign up date, and the numerator in the calculation should be based on date of first deposit.

My noob solution is to add a new column in my data source (Excel), which counts the number of deposits for each sign up month. Something like this:

Then I import this new "countif" column in Tableau and do calculated field = AVG([CountIF]) / count([Customer ID]). This works, but is there an option to do this count if directly in Tableau? The count function I find in Tableau indeed counts when visualizing, but what i really need is counting in the data source itself, and it shows like zeros and ones, and doesn't provide the total count, as Excel does:

Any suggestions on both questions would be much appreciated.

Thanks,

Angel

• 1. Re: Graph based on different column

Hi Angel -

Next time please attach a mock up data to make it easier for the forum to help you out.  If I understand your question fully, you can create a simple LOD to get what you want.  Here is a simple data

 Customer ID Sign Up Date First Order Date 1 1/1/20 1/1/20 2 1/1/20 1/1/20 3 1/1/20 3/1/20 4 2/1/20 2/1/20

The attach model will output the following: 67% in Jan and 100% in Feb

1 of 1 people found this helpful
• 2. Re: Graph based on different column

Hi Budi,

Thanks for the answer. I attach a .twbx file, hope to have done it the right way, it is my first time uploading such.

I believe LODs are the right approach, yes. However, what I need is not only Sign Up Date = First Order date, but also to include all orders from previous months' registrations. Based on your example, in the file I attached, I split the "Yield" calculation on numerator and denominator. Denominator is great, but numerator needs to include all previous values. For instance, number for April 2019 should be 211, and not 163. 163 are only the orders in April with registrations in the same month, and what is needed are those plus orders in April with registrations in previous months. In Excel, that would work with the formula "counif(range First Order Date; column Sign up Date)".

Thank you again,

Angel

• 3. Re: Graph based on different column

hi Angel,

try changing the numerator formulae to <= instead of =

{FIXED [Sign up Date]:COUNT(

IF [Sign up Date]<=[First Order Date]

THEN [Customer ID]

END)}

1 of 1 people found this helpful
• 4. Re: Graph based on different column

Hi Angel -

You will need to self join the tables to imitate COUNTIF.  Check the attached model.

• 5. Re: Graph based on different column

Thanks, Budi. That is what I needed.