1 2 Previous Next 17 Replies Latest reply on Sep 13, 2019 9:53 AM by Hari Ankem

# How to derive another status from original status based on date

Hello,

I have a data set for August, 2019.   The data set has one field: status which consists  of Open and Closed. I need to derive another status: Update. The condition is: Based on the existing Status: Open or Closed, if created (month) = date (month) then status = 'Update'. That means the month should be August. I need to calculate the total count for Update,  I also need the total count for whatever Open or whatever Closed in this data set. Not sure I've made it clearly?

I created the following calculated field, but it didn't get the result as I expected.  It'll be greatly appreciated if you can provide any . better solution. Thank you in advance.

IF [Status] = 'Open' and MONTH([Date]) =  MONTH([Created ]) then 'Update'

ELSEIF [Status] = 'Closed' and MONTH([Date]) = MONTH([Created ]) then 'Update'

ELSEIF  [Status] = 'Closed'

then 'Closed'

ELSEIF  [Status] = 'Open'

then 'Open'

END

• ###### 1. Re: How to derive another status from original status based on date

What is the expected result?

• ###### 2. Re: How to derive another status from original status based on date

It should be like these below:

Thanks for responding.

• ###### 3. Re: How to derive another status from original status based on date

August Date:

MONTH([Date]) = MONTH([Created])

Update include Open and Close (but they must fall in August)

• ###### 4. Re: How to derive another status from original status based on date

I am showing the same number 2085 for Update above.

• ###### 5. Re: How to derive another status from original status based on date

What I mean how to put the three status: Open, closed, Update in the same field ( New status). The original Status only has Open, Closed.  Is it possible?  Thank you.

• ###### 6. Re: How to derive another status from original status based on date

OK! Let me see if I can understand and explain the issue here.

In the month of August, we have these issue counts with the respective status for each group.

If we apply the business logic, all of the above issues will have the new status "Update". All the other issues which were not created in the month of August will continue to retain their status. So, if we don't filter the data for August created date, then we will see the other statuses. In the current scenario, we have only Open status for all the issues that were not created in August, and hence we do not see the Closed status.

• ###### 7. Re: How to derive another status from original status based on date

Hi Hari,

My expected view  should be like this below,  the only difference is that  Update in this picture should be 2085. The picture  has  39  issues count for 'Closed'. The issues count for 'Closed' all fall into August, which should be counted as 'Update', but as the same time they also be counted as 'Closed' even though they fall into August. Open has the same situation, Some of Issues count for Open fall into August, but some are not. They should be all counted as Open. Only those issues count for Open or Closed fall into August should be counted as 'Update'.  Your calculation is very helpful. Thank you very much.

• ###### 8. Re: How to derive another status from original status based on date

Here you go:

I have enabled the Totals as shown below, and updated the Label to "Update."

Hope this helps.

• ###### 9. Re: How to derive another status from original status based on date

Hi Hari,

Great! It is what I want. Thank you for all the help. You have a good night!

Best Regards

Wenyi

• ###### 10. Re: How to derive another status from original status based on date

You are welcome.

• ###### 11. Re: How to derive another status from original status based on date

Hi Hari,

I'm puzzled that how you got the  'Grand Total' is 2085 since you enabled the Totals in Analysis? Shouldn't it be 3323 for total? Thank you.

Regards

Wenyi

• ###### 12. Re: How to derive another status from original status based on date

If you see the Count formula, when there is more than 1 distinct status, the value to be displayed is now different.

IF COUNTD( [Status] ) = 1 THEN //This condition is true for each row since we have only 1 status

SUM( [Issues Count] )

ELSE

SUM( IF [Month Filter] THEN [Issues Count] END ) //This condition is true only for the Grand-Total line and so the actual grand-total is now ignored and computed based on the value here.

END

• ###### 13. Re: How to derive another status from original status based on date

Hi Hari,

Thank you for explanation. It is really helpful. The approach is smart and tricky. I'll do some trial.

Best Regards

Wenyi

1 of 1 people found this helpful
• ###### 14. Re: How to derive another status from original status based on date

Hi Hari,

I have one more question here. I think your solution applies to the Status which has more than one status condition. That said, the Status must have Open and Closed existing, then you can get the New status by this technique. I wonder what about one group only has one Status condition like Open?

You see in the following picture Group F, G, H only have Open for Status. They don't have status for Closed. Only group I has both Open and Closed for Status.

My question is: how to implement this logical statement for Status with only one status (Open or Closed) + New ( issues count in August).

For example, for group F, it has only Open  Status, some of its issues fall into August, so they should be counted New( which is 11 issues count), but the total issues count for Open should be 19 issues count ( issues count in August + issues count in  July).

Thank you.

Wenyi

1 2 Previous Next