12 Replies Latest reply on Oct 19, 2016 10:49 PM by sooyin.tong

# How to calculate Delta value

Hi,

I want to plot the delta value for 'ParameterA' against the Test Time (in hour), and able to get it with the fixed base value = first Test Time.

However, I have stuck when tried to change the base value to be 'Average of parameter A from 25 to 48hour test time'

Appreciate if anyone has the solution to share with.

Thanks...

• ###### 1. Re: How to calculate Delta value

Hey Soo,

I'm not sure if I'm understanding you correctly, but would this calculation work?

sum(if [Test Time (h)]>=25 AND [Test Time (h)]<=48 then [Parameter A] END)/24

-Lisa

• ###### 2. Re: How to calculate Delta value

Hi Lisa,

Thanks for the comment.

The # of records between 25-48hours is not fixed, so can't divide by 24.

You can refer to raw excel data (packaged) for details

Thanks,

• ###### 3. Re: How to calculate Delta value

Soo,

If you're averaging by the amount of records, you can divide by sum([Number of Records]).

-Lisa

• ###### 4. Re: How to calculate Delta value

Lisa,

Is that only sum up 'number of records' within 25-48hrs window ?

Thanks

...SY

• ###### 5. Re: How to calculate Delta value

SY,

Sorry for not being more clear, I mean:

sum(if [Test Time (h)]>24 AND [Test Time (h)]<48 then [Parameter A] END)/

sum((if [Test Time (h)]>24 AND [Test Time (h)]<48 then([Number of Records]) END))

So this sums all of the Parameter A amounts and number of records only within that time window. Probably not the cleanest solution but it should work.

-Lisa

• ###### 6. Re: How to calculate Delta value

Lisa,

This is great..exactly what i am looking for . Thanks !!

However, something is not right when I visualized the data (plot2).

Base value is not constant and mostly 'Null' except Test Time between 24-48hrs.

Did i miss out anything in my calculation ? Anything to do with the filter ?

Thanks,

...SY

• ###### 7. Re: How to calculate Delta value

Hey SY,

Try this for base value calc instead:

window_sum(if attr([Test Time (h)])>24 AND attr([Test Time (h)])<=48 then sum([Parameter A]) END)/

WINDOW_SUM((if attr([Test Time (h)])>24 AND attr([Test Time (h)])<=48 then sum([Number of Records]) END))

This will return a fixed avg across that time period for all time. Additionally, make sure when you're plotting Delta_base@24h you're computing it across the table.

-Lisa

• ###### 8. Re: How to calculate Delta value

Thanks Lisa....

I think is almost there except one more item below.

The base value will be calculated for each dimension placed on Details shelf (serial, location).

How about if I want only one common base value for all serials by location, by week ?

Googled and some suggested LOD expression...

I tried with that (plots 'Base value LOD') and looks good.

However, I am not familiar with LOD and would appreciate your comment.

Thanks,

...SY

• ###### 9. Re: How to calculate Delta value

Hey SY,

That looks good! I always check my calculations by charting them out too

-Lisa

1 of 1 people found this helpful
• ###### 10. Re: How to calculate Delta value

Hi Lisa,

Thank you very much for your assistance to fix my problem.

By the way, I have another issue would appreciate your advice too.

For this case, there is error of ‘cannot blend secondary data source because one or more fields use an unsupported aggregation’when blending 2 tables.

Any idea what's wrong?

I have searched online and tried some workarounds but still can't get rid of this error. Do you know any other solutions ?

http://kb.tableau.com/articles/issue/error-cannot-blend-secondary-data-source

Thanks,

...SY

• ###### 11. Re: How to calculate Delta value

Hey SY,

I'm not sure how to help I know tableau 10.0 has solved a lot of the issues with data blending though.

-Lisa

• ###### 12. Re: How to calculate Delta value

Hi Lisa,

No problem...will explore Tableau10 for any solutions.

Thanks!

- SY