7 Replies Latest reply on Jul 15, 2016 9:42 PM by Ashish Chaudhari

# Conditional Sum (SUMIF) across multiple data sources

Hi Tableau,

I am having problems understanding how to make a conditional sum across two data sources.

DS1 contains Table that holds Date1 and Value

DS2 contains Table that holds Date2

I would like to create a calculated field that for each DS2 record sums Values from DS1 where Date2 >= Date1.

I have tried this:

if ATTR([DS2].[Date])>=ATTR([Date]) then sum([DS2].[Value]) else 0 END

But resulting values are 0.

Any hints?

• ###### 1. Re: Conditional Sum (SUMIF) across multiple data sources

Hi Morten,

if ATTR([DS2].[Date])>=ATTR([Date]) then Zn(sum([DS2].[Value])) else 0 END

I have made small change to the calculation. Please see if it works. Also did you check ATTR([DS2].[Date])>=ATTR([Date]) condition if it evaluates true and false. If not then we need to work on the calculation.

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: Conditional Sum (SUMIF) across multiple data sources

Ashish, thanks, the zn() didnt make any difference, all DS1 Values are not null, however, when I attempted to evaluate the boolean expression I realized that if just one of the DS1 Dates are "False" then it evaluates all false (or zero, if I sum)? What am I missing here?

• ###### 3. Re: Conditional Sum (SUMIF) across multiple data sources

Hi Morten,

You are right. This condition will be applied entire column but even if single date is found which is greater than ATTR([DS2].[Date]) then it should give the sum.

can you post some screenshot by blurring them or sample dummy data which I can try things? is it possible?

-Ashish

• ###### 4. Re: Conditional Sum (SUMIF) across multiple data sources

Hi Ashish,

I have sent the workbook to your gmail. I will upload it to the Forum if things check out.

Best, Morten

• ###### 5. Re: Conditional Sum (SUMIF) across multiple data sources

Hi Morten,

Sorry For the late reply. I have created the custom dates at year level to solves this one. Please refer to the same.

Step 1

Step 2 - Make below selection (Details as "Years" and select Date value radio button)

Similarly I have created custom date at year level for Production date. Refer to the below screenshot.

After that I used blending. Please refer the screenshot which depicts the blended relation between to data source.

After that I have verified if I am getting desired result. Please refer to the screenshot below for the same.

Please refer to the output below to confirm the same.

Just one query I have is do you want sum of Contracted capacity for all the dates or you just want that number constant for whole year.

-Ashish Chaudhari

• ###### 6. Re: Conditional Sum (SUMIF) across multiple data sources

Please find the attached workbook for the same. Let me know if any changes required in this. Sorry for delayed reply.

-Ashish

• ###### 7. Re: Conditional Sum (SUMIF) across multiple data sources

Hi Morten,

If you are looking for below kind of output then please make changes to the Active Capacity Calculation as below

(if ATTR([Settlement Date (Years)]) =  ATTR([DS1].[Production Date (Years)]) then zn(SUM([DS1].[Contracted Capacity]))*COUNTD(DATETRUNC('day', [Settlement Date]))

END)

Refer to the below screenshot which can be desired result that you might look for.

Let me know if this helps. If any of solution has helped you to solve your query, please close the thread by marking "Answer as Correct".

Let me know your feedback on this.

Thanks and Regards,

Ashish Chaudhari