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

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.

Join Cumulative count Individual count New count Closing count Date difference