Build Visualizations for Cash Flow (Invoice Overview)

Visualization 1 - Numeric Point Indicator, Overdue Receivable
Visualization 2 - Numeric Point Indicator, Outstanding Receivables
Visualization 3 - Comparison Bars, Top Overdue Customers
Visualization 4 - Comparison Bars, Overdue by Days
Visualization 5 - Filter and Table, Outstanding Receivables details view

In this lesson, you will be using a data model to create an interactive report with variety of Visualizations. We will built first page Invoice Overview as part 1 of the Visualization lesson.

We have already pre-built some steps to kick start things here.

After opening your story from the previous lesson, click Edit to start editing the story.

If “Optimized View Mode” pop-up shows up then check Notify Only On Next Update and chose No Thanks.

Visualization 1 - Numeric Point Indicator, Overdue Receivable

We shall build our first chart to show “Overdue Receivable %”.

Add a Numeric Point Indicator Chart.

Note: - If you don’t see Insert >> Chart in the top navigation, select More then Chart.

A new Visualization tile will be added, please move that tile as shown in the below image:

Data source “COVID_CASHFLOW_AR_CLEARED_DATA” is already embedded into the story.

Select Numeric Point from Indicator under Chart Structure, then select 2 Measures “% of Overdue Amounts” and “Overdue Amounts” from Primary Values and click on empty space to come out of drop down.

Two Measures can be seen on the chart we selected.

Formatting Numeric values

Go to Styling as showing in the image.

Change Border : All Borders.

Scroll down to Number Format section select Thousand in Scale and k,m,bn from Scale format.
Select Builder icon to get back to the designer.

To activate Threshold Options, hover onto “% of Overdue Amounts” and click Threshold Options, then select View All Thresholds… to see all built in thresholds.

Expand “% of Overdue Amounts”. The Image below show’s the Threshold limits built for “% of Overdue Amounts”.
Collapse the definition and click Done at bottom right of the screen.

Again go back to Threshold options >> Show Threshold >> select Story Defined.

Selecting Story Defined will activate Threshold to the attribute on Tile as shown in the image.

Select title and edit the name to “Overdue Receivables %”.

Visualization 2 - Numeric Point Indicator, Outstanding Receivables

Lets build a 2nd chart to show “Outstanding Receivables”.

Insert a new chart.

Note: If you don’t see Insert >> Chart in the top navigation, select More then Chart.

Align new tile with “Overdue Receivables %” tile as shown in image and Select Numeric Point from Indicator under Chart Structure.

Creating Calculated Column

The Calculated Measures calculation creates a new measure (or value) by applying mathematical formulas to the data in your model. One of the major advantages of creating these formulas in stories instead of model is that we can use it onetime for the particular story.

In the following steps we will create “Number of Invoices” calculated column which will produce count of ACCOUNTING DOCUMENTS.

Select Create Calculation from +Add Measures Drop down under Measures.

It opens a screen like this:

Fill in the following values in the Calculation Editor:
- Type : Aggregation from Drop down
- Name : Number of Invoices
- Operation : COUNT DIMENSIONS
- Aggregation Dimensions : ACCOUNTING DOCUMENT(from model) and click “OK”

Select “Number of Invoices” and “AMOUNT” from +Add Measures drop-down under Measures.

Go to Styling to format Data.

Select Border: All Borders.
Select Scale: Thousand.
Scale Format: k, m, bn.
Select Decimal Places : 0.
Go back to Builder.

Rename Title to “Outstanding Receivables”.

Overdue Receivables % and Outstanding Receivables

The Overdue Receivables % KPI is a common calculation which describes the amount of the invoices that are overdue as a percentage of the total open invoices. We see the total amount of overdue receivables $622.172 million, represent --> change to representing 28% of the total open receivables, which is $2,258.891million. The KPI is shown in red indicating that this needs attention. Generally, an Overdue Receivables % is good when it is less than 10%, in the warning zone between 10% to 25%, and is critical when above 25%. Of course, this could differ for different organizations.

Visualization 3 - Comparison Bars, Top Overdue Customers

In the following lesson we will build a bar graph to analyze Overdue Amounts by Customer.

Add a new chart and align with “Overdue Receivables %”.

Note: If you don’t see Insert >> Chart in the top navigation, select More then Chart.

Select “Overdue Amounts” from Measures and “CUSTOMER” from DIMENSIONS.

Sorting and filtering top 5

Go to More Options >> Rank >> CUSTOMER >> Top 5.

Go to Styling and do the following to format the chart:

Select Border :All Borders.

Set Scale = Thousand, and Scale Format = k, m, bn.

Go back to Builder.

Rename the Title to “Top Overdue Customers”.

Final Result should look like this:

Visualization 4 - Comparison Bars, Overdue by Days

In the following lesson we will create a new chart by using existing bar graph.

Go to More actions >> Copy >> Duplicate.

Do the following changes in the Builder:

Chart Orientation : Vertical.

Measures : Remove “Overdue Amounts” and select “AMOUNT”.
Dimensions : Remove “CUSTOMER” and select “Late Days”.

Under Color, select +Add Dimension/Measure then from the drop down select “Late Days”.

Then from color drop down Red.

We will filter out all 0 days in following step.

Select Add Filter >> select “Late Days” >> Enable Exclude Selected members.
Select 0 to be excluded and click OK.

Rename the title to “Overdue by Days” and final result should like below:

The Top Overdue Customers chart shows the top 5 customers by their total amount of overdue receivables. We see that both customers USCU_S02 and USCU_L09 have over $100 million USD in overdues. The Overdue by Days chart groups all overdue invoices into buckets which represent the number of days they are overdue. The total overdue amount is shown for each bucket. We see that $49.702 million is overdue by 1 – 15 days, and $160.267 million is overdue by 31 – 60 days.

Align all 4 charts as shown in the below image (resize “Overdue Receivables %” and “Outstanding Receivables” so they are smaller).

Visualization 5 - Filter and Table, Outstanding Receivables details view

Add Input Control >> Move it below with “Outstanding Receivables”.

Note: If you don’t see Insert >> Input Control in the top navigation, select More then Input Control.

Click on Page Filter >> Dimensions >> “Late Days” (OVERDUE DAYS).

Select all values Except All Members and (Null) >> Click OK.

Drag the corner of the “Late Days” filter chart to expand it as shown in the image.

Linking necessary charts to filter data according to Input Control

Go to More Options >> Linked Analysis.

Under Linked Analysis please select Only Selected Widgets and Automatically Connect Newly Created Widgets then Apply.

The above options will disconnect filter to all existing Widgets and will only link to any Widgets we are going to create in next step.

Inserting a Table with Overview of Receivables

Select Table from Insert tab and align with two bar graphs as shown in the image below:

Remove “Account” in Rows and select +Add Measures/Dimensions.

Select “ACCOUNTING DOCUMENT”, “COUNTRY”, “NET DUE DATE” from DIMENSIONS.

Similarly remove “Category” from Columns and select +Add Measures/Dimensions.
Select “Account” from DIMENSIONS.

If you select the 1Model Members in Columns we can see “OVERDUE DAYS” attribute is already preselected because it is part of Filter condition that we added in the filter step.

Click on Account(1) in Filters and select “AMOUNT” along with “OVERDUE DAYS”.

Activating Threshold to show color code for data

Hover on “Account” in Columns click on …(More) >> Thresholds>> select Show Threshold.

Selecting date format for “NET DUE DATE”

Hover on “NET DUE DATE” in Rows click on …(More) >> select Hierarchy >> select Flat presentation >> Click Set.

Rename Title to “Outstanding Receivables Detailed View” and final result looks like below image:

Renaming the page

Select the drop down next to Page1 >> select Rename >> Rename Page1 to “Invoice Overview” >> click Rename.

Saving the story

We can save the story by Ctrl + S or Select Floppy drop down on file tab >> click Save.

Click on View on top right corner to view the whole built page.

Next lesson

Build Visualizations for Cash Flow (Invoice Overview)