How to create OTBI analysis of record count based on days range

For example  supplier sites created :  today, last 5 day, last 6-10 days, beyond 10 days

The desired output data would be like below:

 

Supplier Site Status	Today	1 to 5 days	6 to 10 days	Beyond 10 Days
ACTIVE	0	25632	4	382
INACTIVE	0	510	0	0

 

The steps to create analysis are:

 1.       Create analysis as below:

2.       Select the appropriate subject area: ere it is  "Supplier - Supplier Real Time"

3.       Select the columns:

 

 4.       The whole data will be grouped based on supplier site status: active or inactive

 5.       The Column number of days represents the timestamp difference between current date and record

         creation date.

6.       Number represents total number of records for the above timestamp differences.

7.       We actually don’t need the above 2 columns, we have used to check the data.

8.       We will exclude the above two columns from actual output (explained later in this doc).

9.       Write appropriate conditions for each days range:

 Example shown below:

CASE WHEN TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_DATE, "- General"."Created")=0THEN COUNT("- Supplier Site Details"."Supplier Site Code")ELSE 0 END

 

 

One more Example:  for last days 1 to 5

CASE WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "- General"."Created" ,CURRENT_DATE ) >= 1 and TIMESTAMPDIFF(SQL_TSI_DAY,"- General"."Created" ,CURRENT_DATE )<=5 THEN COUNT("- Supplier Site Details"."Supplier Site Code") ELSE 0 END

 

 

Similarly write for other days range.

10.   Now sort the Today column: here it is descending order, so the arrow is downwards

11.   Now go to Results tab to see the output.

12.   We see that all columns are getting populated

 13.   Now click on edit view:

14. In the edit view we need to do 2 things:

a.       Add “aggregate rule” for each column as “Sum”. If we do not do this then by default it takes as “REPORT_AGGREGATE” which will not sum up data in column individually rather it sums of total. But wee need to get sum as per column. So we need to do aggregate rule as “Sum”. Shown in screenshot below : steps highlighted in yellow

 

a.       Exclude the columns: No of days and Number: Drag the columns to excluded area as shown in below screenshot:

15.   Now when we see the output, we would be getting the desired result:

 

 

Save the analysis.