5 Replies Latest reply on Mar 12, 2013 6:20 AM by Brad Llewellyn

# Using WINDOW_SUM function

Hello,

Excuse me for my english.

We have problems with mesures from 2nd level DATA.

Conssidering this datamodel :

Table1 : id_dataTable1 : nameTable1 : salaryTable2 : data1Table2 : data2
5468paul2125xxxdata2
5468paul2125yyydata2

5469

leon1895xxxdata2

If i make mesure name "number of employes" with this formula : COUNTD(id_data), i have no problems

But if i make a mesure name "salary cost" with this formula SUM(salary), results are wrong.

Tableau calculate 4250 for paul and 1895 for leon.

We can explain this because of the jointure of table1 with table2, the occurence for paul is multiply.

We have find a solution by using a formula like WINDOW_SUM(ATTR([salary])). this mesure is using with id_data dimenssion.

But this solution have two problems :

- when you use a mesure with WINDOW_SUM function, the necessary dimenssion for the calcul is not automatiquely provided by tableau on the sheet.

This solution is very diffuclt to use for basic user.

- Performance of table calcul is a bit low.

In conclusion, we search a user-firendly solution for this problems.

Have you another way to provide calcul on data salary with no double ?

How can we automatiquely provide dimenssion necessary for mesure using WINDOW_SUM function ?

• ###### 1. Re: Using WINDOW_SUM function

Beugnetfranck,

Is it possible to alleviate this issue in the ETL process?  Tableau is primarily a visualization tool and is not designed to clean data.

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 2. Re: Using WINDOW_SUM function

In some case we do this, but it's not possible at all.

The objectif of Tableau is to use operationnal Data and evict to tranform data with an ETL.

ETL are expenssive so we use materialized view before tableau extraction.

I don't really want transform the data and just want a way to calculate with a analytic function partitionned by.

But if you know a way in SQL to transform my DATA and resolve this problem, i'm interresting !

• ###### 3. Re: Using WINDOW_SUM function

Is there not a SUM(FIRST(salary)) or SUM(MAX(salary)) possibilty.

Mark Russell

• ###### 4. Re: Using WINDOW_SUM function

SUM(FIRST(salary)) : FIRST can't be use in this context

SUM(MAX(salary)) : You can't use two agregation function

window_SUM(MAX(salary)) : not take a good result

window_SUM(FIRST(salary)) : FIRST can't be use in this context

• ###### 5. Re: Using WINDOW_SUM function

Try

SUM(

IF INDEX() = 1 THEN [Salary] END

)

with Compute Using = [id_data]

Thanks,