13 Replies Latest reply on Oct 10, 2019 2:04 AM by Manoj Lobo

# Is there a way we can identify changes in amount

Hi All,

Can you guys help me to find out difference in amount based on snapshot date,

i need flag when there is change in amount compare to previous snapshot date,flag should be increased,decreased & no change

also i wanna flag for opportunity id when there is atleast one change in that particular oppt_id

attaching sample data.

• ###### 1. Re: Is there a way we can identify changes in amount

I think you asked the same question earlier, if I had to guess the reason you didn't get a response is because you let alone attach a Tableau workbook where you could show what you tried and what you got stuck with, the file you attached is not even a proper excel format which someone can directly open in Tableau (without first opening in excel and doing a save as....)

I have seen people go to great lengths to help people out. It's nothing short of amazing. But expecting them to do this is just not ......... I don't know the word.

I'm just saying that it's the small courtesies that count!

On that note, you should look into the LOOKUP function. It helps you get prior values. Something like LOOKUP(SUM(amount),-1) will give you the value from prior row.

Then you can do a compare to find the increase/decrease.

• ###### 2. Re: Is there a way we can identify changes in amount

Hi Subodh,

Thanks for your reply, next time onwards i will make sure that i will include all details while asking any question.

Regarding above chain, i have attached sample workbook to find out what i want.

1) If there is at least one change for opportunity id like increase or decrease i need another column with as "change". like to identify changed opportunity id

2) counter for opportunity id to check how many times amount value has beed changed for given opportunity id

example if there are 3 entries for id with 2 times increase,1 time decrease then counter is 3.

Hope i clarify .

Thanks

Priyanka

• ###### 3. Re: Is there a way we can identify changes in amount

Hi Priyanka,

Its still not clear about what  you're looking for exactly! Can you provide a sample of your expected output?

According to your original question, You need change in amount compare to previous snapshot date. Something as shown below

if SUM([amount change])>LOOKUP(SUM([amount change]),-1)

then 'Increase'

ELSEIF SUM([amount change])<LOOKUP(SUM([amount change]),-1)

then 'decrease'

else 'no change'

END

But your calculation says something different.

Can you please Clarify? Once that's clear the other two question you asked become easier.

Manoj

• ###### 4. Re: Is there a way we can identify changes in amount

Hi Manoj,

Above calculation seems to be correct for me, i found solution to track increased and decreased opportunities,

only solution i want is for above 2 questions that i have asked counter & another flag to identify for which opportunities changes are happen, so basically in flag 1 column for above example i need to display as "Change"

because there is Increase & decreased for that opp_nmbr

Thanks

Priyanka

• ###### 5. Re: Is there a way we can identify changes in amount

Below is something i m looking for

 Opprtnty Nmbr Snpsht Date Last Day Amount Max Amnt amount change Flag Flag2 Counter 3307460 3/3/2018 null 16028796.62 16005000.02 No Change Change Should be 3 as changes are 3 times in amount 3307460 6/30/2018 16028796.62 31970004.19 15941207.57 Increase Change 3307460 3/4/2019 31970004.19 36311383.32 4341379.129 Increase Change 3307460 3/5/2019 36311383.32 18155691.66 -18155691.66 decrease Change
• ###### 6. Re: Is there a way we can identify changes in amount

Something like this? If yes, please refer the attached workbook

Hope this helps!

Manoj

• ###### 7. Re: Is there a way we can identify changes in amount

Hi Manoj,

I think you have attached different workbook, please check

Thanks

• ###### 8. Re: Is there a way we can identify changes in amount

Hi,

Sorry, Tell me on thing, Do you want Flag W.R.T amount change or max amount?

If you want wrt max amount, make the following changes!

if SUM([Max Amnt])>LOOKUP(SUM([Max Amnt]),-1)

then 'Increase'

ELSEIF SUM([Max Amnt])<LOOKUP(SUM([Max Amnt]),-1)

then 'decrease'

else 'no change'

END

Hope this helps!

Manoj

• ###### 9. Re: Is there a way we can identify changes in amount

My bad! I have attached the workbook in my previous reply with details (It matches the expected output shown by you), Please refer!

Attaching the workbook here again just in case!

Hope this helps!

Manoj

• ###### 10. Re: Is there a way we can identify changes in amount

Hello Manoj,

This is really helpful and working fine, only i have 1 question when i m adding second oppt_id then count column is also changing, please find below screenshot

count should be 4 for 3307460 and 2 for 587261.

hope it clarify.after this i will mark this question as correct

• ###### 11. Re: Is there a way we can identify changes in amount

Hi,

Right click on Count-->edit table calculation-->Specific dimension-->Rest starting every-->Opportunity number.

Do the same for flag1(Copy) and change(Copy)

Hope this helps!

If this reply helps, mark mark the relevant replies as helpful and correct.

Manoj

• ###### 12. Re: Is there a way we can identify changes in amount

Hi Priyanka

If you have a couple of minutes could you please take this quick survey around your experience on the Tableau Community Forums?

Much appreciated
Ciara

[Program Manager | Tableau Community Forums]