5 Replies Latest reply on Aug 31, 2016 6:33 AM by Shinichiro Murakami

# Calculations

Hi Folks,

I am trying to calculate SUM(X) between two events

For example, I have a table like the below

Day          X               events

1               30                    0

2               30                    0

3               30                    0

4               30                    0

5               30                    0

6               30                    1

7               30                    0

8               30                    0

9               30                    0

10               30                    0

11             30                    0

12               30                    0

13               30                    1

14               30                    0

15               30                    0

16               30                    0

17               30                    0

18               30                    2

19               30                    0

20               30                    0

i want to create a graph days between two events and SUM(X) between two events. for the first even I need to calculate SUM(X) until the first event.

The result would be like

day      day between events          sum(X)

6                     6                                         180

13                    7                                         210

18                      5                                      150

Day field is date datatype.

Thanks

• ###### 1. Re: Calculations

Raviteja,

Little bit complicated, but please see attached file.

Create calculated field

[Sum X]

if index()=1 then sum([X])

elseif lookup(sum([Events]),-1)<>0 and lookup(sum([Events]),0)=0

then sum([X])

else sum([X])+previous_value(1)

end

[Count day]

if index()=1 then count([Day])

elseif lookup(sum([Events]),-1)<>0 and lookup(sum([Events]),0)=0

then count([Day])

else count([Day])+previous_value(1)

end

[index]

if attr([Events])<>0 then index() end

Thanks,

Shin

• ###### 2. Re: Calculations

I tried doing the same but it does not work as expected.

I have more field included in the data set

P.S : Day is Date field. There might be missing dates aswell

Day          X               events               Type

1               30                    0                    NULL

2               30                    0

3               30                    0

4               30                    0

5               30                    0

6               30                    1                    A

7               30                    0

8               30                    0

9               30                    0

10               30                    0

11             30                    0

12               30                    0

13               30                    1                    B

14               30                    0

15               30                    0

16               30                    0

17               30                    0

18               30                    2                    C

19               30                    0

20               30                    1                    A

23               30                    1                    A

i want to create a graph days between two events and SUM(X) between two events. for the first even I need to calculate SUM(X) until the first event.

The result would be like (Across all types)

day      day between events          sum(X)

6                     6                                         180

13                    7                                         210

18                      5                                      150

20                        2                                       60

23                         3                                        30

The result when computed for one Type A

6                    6                              180

20                    14                            420

23                    3                                  30

• ###### 3. Re: Calculations

I used table calculation and I cannot cheat Tableau's table calc with skipped days behind the scene in this case..

Filtering A itself works, but again missing "X" (21,22) does not work.

Thanks,

Shin

• ###### 4. Re: Calculations

Hi all,

RAVITEJA, you wrote:

P.S : Day is Date field. There might be missing dates aswell

This implies using Dates (or Day bins) instead of Day numbers,

so one could activate domain padding on a view

by setting Show Missing Values on Date (or Bin) field.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 5. Re: Calculations

Yuri,

That's good idea.

Thank you for the following up.

Shin