5 Replies Latest reply on Dec 7, 2018 1:45 AM by Yuriy Fal

# Need help!!, Calculation with two different dates in a condition.

Hi Everyone, I get some problem with a calculated field, the calculation need to use 2 different dates in condition.

As my example, there are 2 dates for a transaction. (created on, stage create on)

The result from calculation that it should be :

To display the number of users that created and deposit within selected period.

***Let me explain more :

1. If you select filter month is "May" so you would get

First Bar Chart : number of user that has created on May.

Second Bar Chart : number of user that has created on May and has deposit stage (stage 3) within May.

Third Bar Chart : number of user that has created on May and has transfer stage (stage 6) within May.

2. If you select filter month are "Feb-May" so you would get

First Bar Chart : number of user that has created on Feb until May.

Second Bar Chart : number of user that has created on Feb - May, and has deposit stage (stage 3) within Feb - May.

even user that created on Feb and deposit on others month (selected month) would be count in this calculation.

So, this is a calculated field that I has created.

COUNTD(

IF [Stage No] = '3' //user with stage deposit

AND YEAR([Created On]) = YEAR([Stage Created On])

AND MONTH([Created On]) = MONTH([Stage Created On])

THEN [Opportunity Id]

ELSE NULL

END

)

1. For Single month selected : the result is correct, equal to a result with query from database

2. But when multiple month are selected : the result is incorrect, when compare with query from database

(result from calculated field return number of user which has deposit stage in each month, and sum each month to total.)

(But the way that I hope is to get number of user which has deposit stage within selected period (not in each month and then sum each value of month)

The Correct Answer Should be like sheet : Opt4.2_Overall_by_crossDate (single view should be)

Have you ever got the same or likely this problem, or if you have any solution to solve it.

• ###### 1. Re: Need help!!, Calculation with two different dates in a condition.

Maybe you need toggle "add to context" for the filter to make it to work.  Sorry I do not have a tableau desktop license, so I can not open your workbook.

• ###### 2. Re: Need help!!, Calculation with two different dates in a condition.

I'm not sure which filter should set as context filter. For the second picture is example that the result come correctly.

But infact date filter should have only one date dimension.

The way that I think is

when main date filter (created on) is picked. It should affect to another filter dates dimension (stage created on) within the same period. Is it possible, still find the way to solve this.

Thanks,

Yut

• ###### 3. Re: Need help!!, Calculation with two different dates in a condition.

Hi Ronnakot,

You may want to transform your data first,

namely, to Un-Pivot the Date columns

(btw, in Tableau this option is called Pivot ;-),

then use a LOD expression to count (distinct) Stages

for each (distinct) Opportunity Id.

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 4. Re: Need help!!, Calculation with two different dates in a condition.

Hi Yuri,

Next step, I need to try this solution on my workbook which has about 5 tables left join with it. Hope It still work fine.

Regards,

Yut

• ###### 5. Re: Need help!!, Calculation with two different dates in a condition.

Yut, you're welcome.