7 Replies Latest reply on Jan 28, 2019 12:56 AM by Shivangi Aggarwal

# Urgent

Hi ,

I have a stock data as shown below. I want to calculate Difference between close-open. value of close should be choosen as --- close value corresponding to last date of stockname (MAX(D)) and open value should be ---- open value corresponding to first date of stock (MIN(D)) .there are different types of stock table . I am unable to fetch this data . Please help me as soon  as possible as its really urgent.

I am able to execute the same in POWER BI in this way

ClosingAmount =

VAR maxDate = MAX(Stocks[Date]) RETURN

CALCULATE(sum(Stocks[Close]),Stocks[Date]=maxDate)

but not able to execute i tableau

• ###### 1. Re: Urgent

Max Close Value:

Calc: IF {Fixed stockname: max(D)}=D

THEN Close END -> this will give the close value of max date per store name

Min Open Value:

Calc:IF {Fixed stockname : MIN(D)}=D

THEN Open END --> This will give the open value of min date per store name

Difference

Max Close Value - Min Open Value

Hope this helps.

If you want filters to affect the calculation add the filter to context.

Check tableau order of operation in google it will help you understand the Filter stages and when they will apply.

Regards,

NB

1 of 1 people found this helpful
• ###### 2. Re: Urgent

Any chance you can supply the data file to help with ?

I don't see how that Power BI syntax is going to work, there's no column name [Date}

• ###### 3. Re: Urgent

Hi ,

Thanku for Your quick reply . I am able to get MAXCLOSE VALUE and MIN OPEN value but not getting difference when apply calculation field if difference its not showing any result . Actually I am very much new to Tableau . • ###### 4. Re: Urgent

SUM(Max closed ) - SUM(Min Open)

Instead of Max closed - open

BR,

NB

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

Hi, I am bit confused why we are using Aggregation function sum while calculating the difference . Acc. to me SUM(Max closed ) - SUM(Min Open) it will first calculate the sum of MAX CLOSED then calculate the difference . I am not getting how it works .

for example:-

I have to calculate the gain percent - should i write like this :-

([DIFFERANCE]/SUM([MINOPENVALUE]))*100

• ###### 6. Re: Urgent

yes you should aggregate the data then fine the difference

(SUM(Max closed ) - SUM(Min Open)/

SUM(Min Open))*100

• ###### 7. Re: Urgent

Hi ,

But why aggregation is required ?