1 2 Previous Next 17 Replies Latest reply on May 26, 2018 7:18 AM by Zhouyi Zhang

# Calculate Active at end of every month

Hi Friends,

I am trying a line chart which shows - how many tickets Opened(created), closed and active (open) tickets at end of every month (for example at end of 31 mar 11:59 PM). I created open event and closed event views in SQL and able to bring the Opened and Closed count for every month even though we have 2 separate date columns (Created On Datetime for Opened and Completed On for Closed).

I am not able to bring the Active @ EOM since 1000 tickets would have been active at end of Feb but now this value will not stay just like that since these 1000 might have got closed in March. Instead of seeing how many active ticket we currently have, I want to see the count of tickets that was open at the End of every month.Any suggestions.

• ###### 1. Re: Calculate Active at end of every month

--------If you want to know the count on Last day of every month ----See below

For example "Only on last day of any month "will be like this

2) Condition =DATEPART('day',[Order Date])=[Last day of the month] --->Set this as True

This shows me correctly that on 31st of 2014 we had only one orders (same is the case with other months )

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 2. Re: Calculate Active at end of every month

Please set the answer as CORRECT & HELPFUL if it really helped to sort your query so that it can help others as well.

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 3. Re: Calculate Active at end of every month

Hello Vijay,

Did it solve your issue as well ?

Thanks,

Ritesh

• ###### 4. Re: Calculate Active at end of every month

On Tue, 24 Apr 2018 at 6:24 AM, Ritesh Bisht <tableaucommunity@tableau.com>

• ###### 5. Re: Calculate Active at end of every month

Can you please close the thread by setting it as CORRECT ?

Thanks,

Ritesh

• ###### 6. Re: Calculate Active at end of every month

Apologies Ritesh.. I have prepared the sample data and the result I am trying to achieve here. Please have a look at this and let me know your suggestions.

I am trying to achieve the below in the form of 3 lines in same graph. Is this possible?

from the below looking data

• ###### 8. Re: Calculate Active at end of every month

Hi Vijay,

I think you mean like below

Please find the attachment.Hope it helps !

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 9. Re: Calculate Active at end of every month

Please mark it as CORRECT and HELPFUL if it really helped you so that it can help others as well.

Thanks,

Ritesh

The Slogger Blogger at www.tableauinfo.com

• ###### 10. Re: Calculate Active at end of every month

Hi Ritesh, thanks aging for helping.. I downloaded the result you provided and tried to do the same using the sample data I gave and I am able to do the same. But when tried doing it in my live data I am not getting the result. Dont know what I am doing wrong. Hence I have prepared a packaged workbook with the real live complete data with ticket numbers and dates.

Kindly have a look and provide your suggestions. Also I actually want to do this as a line chart with 3 lines for each measure.

• ###### 11. Re: Calculate Active at end of every month

Hi Vijay,

Please find the attached 10.5 version

Hope that you have the correct answer now !

I can see for the month of Jan you have Created =90 and Active=82 and Resolved=8

I will check the line part later

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 12. Re: Calculate Active at end of every month

Extraordinary solution.. I have been searching for a solution for months. Thank you so much..

But Ritesh I am not able to plot this as Lines since I dont have the dates as axis.. Can you suggest on how to achieve..

• ###### 13. Re: Calculate Active at end of every month

Hi Ritesh,

Unfortunately the counts are not matching up. I have created two independent worksheets one for created count and another one for resolved. And attached the same packaged wb here for you.. Can you please check and see if we are missing something

• ###### 14. Re: Calculate Active at end of every month

Can't open it, I am using 10.5 V

Ritesh

1 2 Previous Next