8 Replies Latest reply on Mar 31, 2017 9:52 AM by Lisa Li

# how to substract the first date with information and the last day with infomartion

Hi, tableau community!

I have been working on a way to sum the first column that has some information on it and the last one.

the problem is that every case is different I will attach an image of my workbook, I have 7 months,

and I'm looking for a way to know the difference between the first number they got on any month and the last number they got.

Regards,

Luna

• ###### 1. Re: how to substract the first date with information and the last day with infomartion

Hello Karen,

You can try a level of detail calculation.

{Fixed [Client]: max([Date]) } to find the max date for that client, or {Fixed [Client]: min([Date]) } to find the min date for that client.

Now, you can use both calculations to set up two if statements where

1. Return the max date's measure

if [Max Date] = [Date] then [Measure] end

2. Return the min date's measure

if [Min Date] = [Date] then [Measure] end

then create another calculated field that subtracts the two.

Hope this helps!

-Lisa

2 of 2 people found this helpful
• ###### 2. Re: how to substract the first date with information and the last day with infomartion

Thanks so much! it works perfectly!

• ###### 3. Re: how to substract the first date with information and the last day with infomartion

Hi!

sorry to bother you again... I can do the formulas to obtain the MAX date and MIN date but... when I want to divide them or subtract them it won't work ..

my formula is just

[MINI DATE] / [MAXI DATE]

it won't work... I attached an image of what happens ..

do you know how I could solve this problem????

thank you so very much for your help!

regards,

Luna

• ###### 4. Re: how to substract the first date with information and the last day with infomartion

Hello Karen,

If you don't have [Date] in the columns field, the calculations should work. The reason they don't right now is because there is no time when a max date exists at the same time a min date exists, so there are multiple rows for a given Client.

Do you need to view the measures for every month for each client?

-Lisa

1 of 1 people found this helpful
• ###### 5. Re: how to substract the first date with information and the last day with infomartion

Hi, Lisa!

Thank you so much for your response!

I don't need to see the dates, I took them off but it won't work, the only number that is displayed is for the clients that only have 1 transaction.

for the clients that have more than 1 transaction during the 6 months, it appears like null.

I don't know what to do in order to make it work... if you have any solution you could help me with I will appreciate it so much!

Thanks for your help!

- Luna

• ###### 6. Re: how to substract the first date with information and the last day with infomartion

Hello Karen,

Can you try

sum(if {fixed [Customer Name]: MIN([Order Date])}=[Order Date] then [Sales] END)

/

sum(if {fixed [Customer Name]: MAX([Order Date])}=[Order Date] then [Sales] END)

or:

sum([Min Date Value]) / sum([Max Date Value])

If that doesn't work, would you be able to upload a twbx file with some fake data?

-Lisa

2 of 2 people found this helpful
• ###### 7. Re: how to substract the first date with information and the last day with infomartion

Thanks very much!

it works perfectly!

you rock!

thanks you so much for your help!

regards,

Luna.