8 Replies Latest reply on Dec 20, 2016 9:33 PM by Shinichiro Murakami

# Creating a calculation field in Union data

Hi,

I have set up the data sources in a Union with data sheets then joined them into a primary data,

I need to split columns that are under one column now as I can apply a calculation separately on the data that are in another column.

How can I swap the data value (date) position ( eg. 6   13  to 13     6) and create a calculation field on this data set as I need to put the comparison from the last week sales to this week sales.

For instance]

"Total Transactions" is one Mesure Values and "Tuesday" has two columns that I need to change the data value with a calculation applied as below

--------------------------------------------------------------------------------------------------------------------------------------------

Tuesday

13 (this represents a date)     6(date_last week)

309                                        250=> -24%    Calculation requires  [ SUM(309)/ SUM(250) -1] to get % Value in these columns

74                                          5=> -1390%

47                                         31 => -52%

54                                         19=> -184%

69

72                                          67=> -7%

62                                          59=> -6%

So on,,,

I've attached workbook and data sources for your review.

Your help is much appreciated.

Eric

• ###### 1. Re: Creating a calculation field in Union data

Hi Eric

See below illustrations.

Current Connection

Should be

Then formula should be

[Trans Day Percentage of previous week]

sum([Total Transactions])/lookup(sum([Total Transactions]),-7)

In the attached file, I used previous date because it does not include enough dates.

[Trans Day Percentage of previous day]

sum([Total Transactions])/lookup(sum([Total Transactions]),-1)

Thanks,

Shin

• ###### 2. Re: Creating a calculation field in Union data

Hi Shin,

Thanks for your reply and help me out, Seems I attached the wrong file,

I've attached the correct file that I was working on.

The data are already in Union and Joined as you did.

As a new data will be added on each day, I need a comparison of the same day last week sales aginst today's sales growth as in % .

For instance]

Looking at 'Tuesday' total_transactions, 6 is the date of last week Tuesday and 13 is the date of this week Tuesday (at that time when I was creating this report ).

So whatever the figures shows under '13' displays as it is, but '6' Tue, '7' Wed  and '8' Thur need to be displaying % with a calculation applied of SUM([13th Tue]) / SUM ([6th Tue]) - 1   and keeps on going for 'Wednesday' and 'Thursday' and so on.

Not the same data but the logic is similar to this below

Sorry for the confusion,

Your help is much appreciated.

Eric

• ###### 3. Re: Creating a calculation field in Union data

Oh, you still attached wrong file, twb does not work, please attach twbx.

Thanks,

Shin

• ###### 4. Re: Creating a calculation field in Union data

Sorry about that, here it is

FYI

6 => Tue, 6the Dec

13 => Tue, 13th Dec

Many thanks,

Eric

• ###### 5. Re: Creating a calculation field in Union data

Hope below explanation helps.

Let me know it's still unclear.

The main idea to show different measure in the same pane is explained in here.

Such a simple view, it required 100 steps!! - Having Multiple KPIs - - Still Struggling with Excel ?? <Tableau's Room>

Then, I added some illustrations to explain calculated filed and index.

BTW, I don't know the way to show  % change and this week volume for transaction and add one more measure of meal on right of the table.

I separated Transaction and Meal in different worksheet and merged on dashboard.

[weekday]

[Day number]

[Index]

index()

index's role

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Creating a calculation field in Union data

Thank you very much Shin,

I've learned many from you and I really appreciate your time and support in helping me.

Eric

• ###### 7. Re: Creating a calculation field in Union data

Happy to hear that and enjoy Tableau!

Thanks,

Shin

• ###### 8. Re: Creating a calculation field in Union data

And could you mark one of the answers as correct if you think it's solved?

Thanks,

Shin