Get day wise new addition of counts from the data with day wise closing balance of counts

Modified on Mon, 12 Oct 2020 at 08:47 PM

Scenario:

Data contains day wise cumulative (closing balance) corona cases for all the states.


Requirement:

Get day-wise new corona cases from the cumulative (closing balance) data




Sample Data:

Sample closing count data file is attached herewith.


Learning from the exercise


Solution:

In the sample dataset attached herewith, the day-wise closing balance of the no. of corona cases are given. 

To get day-wise new addition of corona cases, we need to get the difference of the no. of cases of current date to the previous date. To achieve this, we need to compare the current date to previous date.


Steps:


1. Create a copy of the current dataset (CoronaDataClosingCount). This is for generating the new date.

    •Select the dataset from the dataset list and click on Copy icon from the toolbar.

    •Rename it as ‘CoronadataNextDayClosingCount’

2. In the new dataset (CoronadataNextDayClosingCount), convert the Date into date type if the date is detected as string while creating the dataset.


3. In the new dataset (CoronadataNextDayClosingCount), create a new date column (NextDate) by adding 1 to the current date.

    •Right click on the Date column -> Add column -> Custom

    •Give expression as NextDate = dateAdd(“d”,1,Date)



4. Rename the ‘DaywiseClosingNumberofCases’ column in the CoronadataNextDayClosingCount dataset.

    •Go to Result set ->  Manage columns -> click on the column name and rename it to ‘NextDayClosingNoOfCases’


5. Open the original dataset (CoronaDataClosingCount) and add the new dataset (CoronadataNextDayClosingCount) using Add dataset(s) option.


6. JOIN both the datasets with conditions as 

       Date = NextDate and NameOfStateUT = NameOfStateUT (State is to get the state wise count)



7. After the Join, to get the difference of corona cases (between the two dates),

    •Right click on any column -> Add column -> Custom

    •Give the expression as NewCasesPerDay = DaywiseClosingNoOfCases – NextDayClosingNoOfCases


This will give the daywise individual count from a cumulative data.

New Cases per day









Note: This article is based on Smarten Version 5.0. This may or may not be relevant to the Smarten version you may be using.


Dataset Join New count SSDP Closing balance Cumulative sum New cases Corona cases

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article