Dataset Items for version 2021.4 and older

This section describes all visible items in the LS Insight dataset for version 2021.4 and older. From version 2022.1 we provided new report templates and and updated model so we created a new dataset item list.

Table or measure group Available in report Item name Description and/or origin of data

Actuals count

Sales AVG Nr of Items per Transaction AVG Sales per Transaction based on Item Quantity from the source tables.columns (Trans_ Sales Entry.[Quantity], Sales Invoice Line.[Quantity] & Sales Cr_Memo Line.[Quantity])
divided by distinct count of TransactionNo from the source tables (Trans_ Sales Entry.[Transaction No_], Sales Invoice Line.[Document No_] & Sales Cr_Memo Line.[Document No_])
    AVG Transaction per Staff AVG Transaction per Staff based on distinct count of TransactionNo from the source tables (Trans_ Sales Entry.[Transaction No_], Sales Invoice Line.[Document No_] & Sales Cr_Memo Line.[Document No_])
divided by Transacting Staff witch is derived from the source tables.columns (Trans_ Sales Entry.[Transaction No_], Sales Invoice Line.[Document No_] & Sales Cr_Memo Line.[Document No_])
    Item Quantity Item Quantity from the source tables.columns (Trans_ Sales Entry.[Quantity], Sales Invoice Line.[Quantity] & Sales Cr_Memo Line.[Quantity])

Actuals Margin

Sales AVG Margin per Item Sold AVG Margin per Item Sold
    AVG Margin per Transaction AVG Margin per Transaction based on distinct count of TransactionNo from the source tables (Trans_ Sales Entry.[Transaction No_], Sales Invoice Line.[Document No_] & Sales Cr_Memo Line.[Document No_])
    Margin Margin = [Net Sales] - [Cost]
    Margin % [Margin] divided by [Net Sales]

Member Management

Sales Lost Member In the Filter page in member filters there is the option to adjust day’s criteria for lost members. The Lost Member measurement is a count of previously active members that have not made any transactions for the day’s criteria.

 

Sales Margin Increase due to Loyalty Members Margin amount increase due to loyalty members.

 

Sales Margin% Increase due to Loyalty Members Margin % increase due to loyalty members.

 

Sales Member Margin Margin amount from member transactions

 

Sales Member Margin % Margin percentage from member transactions

 

Sales Member Sales Sales amount from member transactions

 

Sales Member Sales ratio Sales from member transactions as a percentage from total sales for the selected period.  

 

Sales New Member Count of member transactions for the selected period having no prior transaction history.

 

Sales Non-Member Sales Net Sales not linked to a member card

 

Sales Recovered Members Count of members that have been declared lost (see definition for Lost Members above) and have returned in the selected period.

 

Sales Returning Members Count of member transactions from members with previous transactions in the system

 

Sales Returning Members % Percentage of returning members from the total count of members.

 

Sales Sales Increase due to Loyalty Members Sales amount increase due to loyalty members.

 

Sales Sales New Members Sales amount to members in the selected period that have no prior sales history.

Actuals Net Sales

Sales AVG Net Sales per Item Sold AVG Net Sales per Item based on NetSalesAmountLCY from the source tables.columns (Trans_ Sales Entry.[NetAmount], Sales Invoice Line.[NetSalesAmountLCY] & Sales Cr_Memo Line.[NetSalesAmountLCY])
divided by
source tables.Columns (Trans_ Sales Entry.[Quantity], Sales Invoice Line.[Quantity] & Sales Cr_Memo Line.[Quantity])
    AVG Net Sales per Staff AVG Net Sales per Staff based on NetSalesAmountLCY from the source tables.columns (Trans_ Sales Entry.[NetAmount], Sales Invoice Line.[NetSalesAmountLCY] & Sales Cr_Memo Line.[NetSalesAmountLCY])
divided by Transacting Staff witch is derived from the source tables.columns (Trans_ Sales Entry.[Transaction No_], Sales Invoice Line.[Document No_] & Sales Cr_Memo Line.[Document No_])
    AVG Net Sales per Transaction AVG Sales per Transaction based on NetSalesAmountLCY from the source tables.columns (Trans_ Sales Entry.[NetAmount], Sales Invoice Line.[NetSalesAmountLCY] & Sales Cr_Memo Line.[NetSalesAmountLCY])
divided by distinct count of TransactionNo from the source tables (Trans_ Sales Entry.[Transaction No_], Sales Invoice Line.[Document No_] & Sales Cr_Memo Line.[Document No_])
    Cost CostAmountLCY from the source tables (Trans_ Sales Entry.[CostAmount], Sales Invoice Line.[CostAmountLCY] & Sales Cr_Memo Line.[CostAmountLCY])
    Net Sales NetSalesAmountLCY from the source tables.columns (Trans_ Sales Entry.[NetAmount], Sales Invoice Line.[NetSalesAmountLCY] & Sales Cr_Memo Line.[NetSalesAmountLCY])
    Transaction Quantity Distinct count of TransactionNo from the source tables (Trans_ Sales Entry.[Transaction No_], Sales Invoice Line.[Document No_] & Sales Cr_Memo Line.[Document No_])

Adjustment

Inventory Adjustment Shows the total adjustments made to item inventory.

([Positive Adjustment] + [Negative Adjustment])

 

  Negative Adjustment Shows the negative adjustments made to item inventory.

 

  Positive Adjustment Shows the positive adjustments made to item inventory.

Budget dimension

Sales Budget Description [Description] from source table [Retail Sales Budget Name]
    Budget Name [Name] from source table [Retail Sales Budget Name]
    Revision [Revision] from source table [Retail Sales Budget Name]
    Status [Status] from source table [Retail Sales Budget Name]

Company

Sales Company Name  

Customer

Sales Address Address from source table Customer
    Address2 Address2 from source table Customer
    City City from source table Customer
    Country Region Code Country Region Code from source table Customer
    County County from source table Customer
    Credit Limit LCY Credit limit in local currency
[Credit Limit (LCY)] from source table Customer
    Customer Name [Name] from source table Customer
    Customer No [No_] from source table Customer
    CustomerNo Name Concatenated Customer_No  and Name from source table Customer
    Payment Terms Code [Payment Terms Code] from source table Customer
    Post Code [Post Code] from source table Customer
    Sales Person Code [Salesperson Code] from source table Customer
    Territory Code [Territory Code]

Date

Sales Current Month TRUE if month and year in visual is current month and year, else FALSE
  Sales Current Quarter TRUE if Quarter and year in visual is current Quarter and year, else FALSE
  Sales Current Week TRUE if week and year in visual is current week and year, else FALSE
  Sales, Inventory Date List of all dates between min and max dates in Sales measure group
  Sales Date Format Gives the same results as Date, Date in date format
  Sales FY Month Financial year month based on the assumption that the financial year is June to June
  Sales Is Weekend TRUE if date in visual is weekend (Saturday or Sunday), else FALSE
  Sales, Inventory Month Month represented with 3 characters (Jan, Feb, Mar…)
  Sales, Inventory Month Day Number Integer number representing the day of month
  Sales Month Days Integer number representing the total days in selected month
  Sales, Inventory Month Number Integer number representing the month number
  Sales, Inventory Quarter Quarter number in the format (Q1, Q2, Q3, Q4)
  Sales, Inventory Quarter Day Number Integer number representing the day of quarter
  Sales Quarter Days Integer number representing the total days in selected quarter
  Sales QuarterNumber Decimal number representing the quarter number
  Sales Sequential Day Number Sequential day number for all dates in date table, less February 29th
  Sales This Year This year based on the maximum date in the Sales fact table.
  Sales, Inventory Week Week number in the format W1, W2….W52
  Sales, Inventory Week Day Weekday represented with 3 characters (Mon, Tue, Wed…)
  Sales, Inventory Week Day Number Weekday number, Sunday = 1, Monday = 2 etc.
  Sales, Inventory Week Number Integer number representing the week number
  Sales, Inventory Year Day Number Integer number representing the day number of the year (1-365)
  Sales Year Month Number Sequential decimal number for all months in date table staring with 1
  Sales, Inventory Year Number Year in integer format
  Sales Year Quarter Number Sequential decimal number for all quarters in date table staring with 1

Global Dim 1

Sales Description [Name]  from source table [Dimension Value] where [Global Dimension No_] is equal to 1

Global Dim 2

Sales Description [Name]  from source table [Dimension Value] where [Global Dimension No_] is equal to 2

Inventory

Inventory Closing Inventory Shows the inventory status (Item count) for each day.

If no transactions occur during a period, the last known inventory status is shown.

 

 

Closing Iventory Average Store Shows the inventory status (Item count) for each day for the average store.

 

  Closing Inventory Cost Shows the inventory cost for each day.

If no transactions occur during a period, the last known cost is shown.

Total Item cost is the accumulated cost of items over time.

For example, 10 items are purchased at unit cost of 2 LCY on day one, and then 5 items are sold but the cost is registered at 1 per unit. The result is Inventory cost of 10*2 - 5*1 = Inventory cost is 15 LCY.

 

  Closing Inventory Value Shows the inventory value for each day.

If no transactions occur during a period, the last known inventory value is shown.

Value is the last known sell price of an item.

 

  Inventory Margin Margin is derived by subtracting the Inventory cost from the Value

([Closing Inventory Value] - [Closing Inventory Cost])

 

  Inventory Movement This calculation shows the movement of items from the previous period. Movement is measured in item count.

A period can be year, quarter, month, or date.

 

  Inventory Turnover Inventory Turnover is a measure of the number of times inventory is sold and replaced during a period. This ratio is calculated by dividing Sales by Closing Inventory for the selected period.

 

  Margin% Margin% is calculated as ([Value] - [Cost]) / [Value]

 

  Out of Stock Days Returns a count of items going out of stock.

That is, when an item goes from positive count in Inventory to zero or below.

 

  PP Inventory Shows the inventory status (Item count) for the previous period.

For example, if visual is on a month level, this will show last month. If visual is on a day level, this will show last day.

 

  PY Closing Inventory Cost Shows the Inventory cost for the previous year. For example, if you are looking at June for any year, this will show Inventory cost for June in the previous year.

 

  PY Closing Inventory Value Shows the Inventory value for the previous year. For example, if you are looking at June for any year, this will show Inventory value for June in the previous year.

 

  PY Inventory Margin Shows the Inventory margin for the previous year. For example, if you are looking at June for any year, this will show Inventory margin for June in the previous year.

Item

Sales, Inventory Base Unit of Measure [Base Unit of Measure] from source table [Item]
  Sales, Inventory Category [Description] from the source table [Item Category]
  Inventory Days from Last Sale Number of days from today to the last sales day for the selected item(s)
  Sales, Inventory Description [Description] from the source table [Item]
  Sales, Inventory Division [Description] from source table [Division]
  Inventory First Purchase Date The first registered day of purchase for the selected item(s)
  Inventory Last Purchase Date The last registered day of purchase for the selected item(s)
  Inventory Last Sales Date The last registered day of sale for the selected item(s)
  Inventory Last Sell Price The last registered sales price for the selected item.

NOTE - value only appears for one item.

  Inventory List of Items Provides a list of the first 5 item numbers and names, and a count of how many remain.
  Sales, Inventory Inventory Posting Group [Inventory Posting Group] from source table [Item]
    Item Description Same as Description…
    Job Item Type NULL… from the source table [Item]
  Sales, Inventory No & Description Concatenation of [No_] and Description from the source table [Item]
  Sales, Inventory Product Group From the source table [Product Group]
    Sales Purchase Item Type [Product Group Code] from the source table [Item]
    Segments 5 Clusters for Segmentation over Margin and Nr of Items
    Standard Cost [Standard Cost] from the source table [Item]
    Unit Cost [Unit Cost] from the source table [Item]
    Vendor Item No [Vendor Item No_] from the source table [Item]
    Vendor & No [Vendor No_] from the source table [Item]

Item Category

Sales Code [Code] from source table [Item Category]
    Description [Description] from source table [Item Category]
    Division Code [Division Code] from source table [Item Category]

Item Variant

Inventory Code Item variant code

 

  Description Item variant description

 

  Description 2 Item variant secondary description

Location

Sales, Inventory City City of location

 

Sales, Inventory Country Code Country code of location

 

Sales, Inventory County County of location

 

Sales, Inventory Latitude Latitude of location

 

Sales, Inventory List of Location Code Provides a list of the first 5 location numbers and a count of how many remain.

 

Sales, Inventory List of Locations Provides a list of the first 5 location names and a count of how many remain.

 

Sales, Inventory Location Code Location code

 

Sales, Inventory Location Hierarchy Provides a hierarchy:

Store Name -> Location Name

(a store can have multiple locations).

 

Sales, Inventory Location Name The name of the location.

 

Sales, Inventory Longitude The longitude of the locations.

 

Sales, Inventory Postcode The postcode of the locations.

 

Sales, Inventory Reporting Store This is the logical store name for one or more locations. Could also be a single store or location.

 

Sales, Inventory Reporting Store ID This is the logical store ID for one or more locations. Could also be a single store or location ID.

KPI's targets

Sales POP Target Growth % Period over Period % growth

 

  POP Target Growth Period over Period growth

POP Margin

Sales MOMTD Margin Margin change from previous month

 

  POP Margin Period Over Period margin (automatically selects year, quarter, or month)

 

  POP% Margin Period Over Period % margin (automatically selects year, quarter, or month)

 

  QOQ Margin Quarter Over Quarter margin

 

  QOQTD Margin Quarter Over Quarter To Date margin

 

  YOY Margin Year Over Year margin

 

  YOYTD Margin Year Over Year To Date margin

POP Net Sales

  POP AVG Net Sales per Staff POP AVG Net Sales per Staff

 

  POP AVG Net Sales per Transaction POP AVG Net Sales per Transaction

 

  POP net sales Period Over Period Net Sales (automatically selects year, quarter, or month)

 

 

POP% net sales

Period Over Period % Net Sales (automatically selects year, quarter, or month)

 

  QOQ net sales Quarter Over Quarter Net Sales

 

  QOQTD net sales Quarter Over Quarter To Date Net Sales

 

  YOY net sales Year Over Year Net Sales

 

  YOYTD net sales Year Over Year To Date Net Sales

POS Terminal

Sales Description [Description] from source table [POS Terminal]
PP counts Sales PM Transacting Staff LM Transacting Staff

 

  PM Transaction Quantity LM Transaction Quantity
    PP AVG Transaction per Staff LP AVG Transaction per Staff
    PP Transacting Staff LP Transacting Staff
    PP Transaction Quantity LP Transaction Quantity
    PY AVG Transaction per Staff LY AVG Transaction per Staff
    PY Transacting Staff LY Transacting Staff
    PY Transaction Quantity LY Transaction Quantity

PP Margin

Sales PMTD Margin Last months margin from start of month to the parallel date from current month
(e.g. If today is marsh 15th, the margin is given for the period April 1st to April 15th)
    PP Margin Last Period Margin. Switches between LM, LQ and LY Margin
    PQTD Margin Last quarter margin from start of quarter to the parallel date from current quarter
(e.g. If today is may 15th, the margin is given for the period January 1st to February 15th)
    PYTD Margin Last year margin from start of year to the parallel date from current year
(e.g. If today is may 15th, the margin is given for the period January 1st to May 15th last year)
PP Net Sales Sales PM AVG Net Sales per Staff LM AVG Net Sales per Staff
    PM AVG Net Sales per Transaction LM AVG Net Sales per Transaction
    PM AVG Transaction per Staff LM AVG Transaction per Staff
    PP AVG Net Sales per Staff LP AVG Net Sales per Staff

 

  PP AVG Net Sales per Transaction LP AVG Net Sales per Transaction

 

  PY AVG Net Sales per Staff LY AVG Net Sales per Staff
    PY AVG Net Sales per Transaction LY AVG Net Sales per Transaction
    PMTD net sales Last months Net Sales from start of month to the parallel date from current month
(e.g. If today is marsh 15th, Net Sales is given for the period April 1st to April 15th)
    PP net sales Last Period Net Sales. Switches between LM, LQ and LY Net Sales
    PQTD net sales Last quarter Net Sales from start of quarter to the parallel date from current quarter
(e.g. If today is may 15th, Net Sales is given for the period January 1st to February 15th)
    PYTD net sales Last year Net Sales from start of year to the parallel date from current year
(e.g. If today is may 15th, the Net Sales is given for the period January 1st to May 15th last year)
    PP net sales Previous Period Net Sales (automatically selects year, quarter, or month)
Purchases Inventory Purchase Quantity Shows the purchase quantity (Item count) for the selected period.

 

  Purchase Return Quantity Shows the number of items returned to vendors.

NOTE - View with reason description to get more details.

    Purchase Return Rate Shows the percentile of items returned to vendors.

NOTE - View with reason description to get more details.

    PY Purchase Return Shows the number of items returned to vendors the previous year (12 months prior to selection).
Reason Codes Inventory Code Reason codes, for purchase returns and sales returns.

 

  Description Reason codes, for purchase returns and sales returns.
    List of Reasons Provides a list of the first 5 Reason descriptions and a count of how many remain.
Sales Inventory PY Return Quantity Shows the number of sold items returned the previous year (12 months prior to selection).

NOTE - View with reason description to get more details.

    PY Sales Quantity Shows the number of items sold the previous year (12 months prior to selection).
    Return Quantity Shows the number of sold items that have been returned.

NOTE - View with reason description to get more details.

    Return Rate Shows the percentile of sold items that have been returned.

NOTE - View with reason description to get more details.

    Sales Quantity Shows the number of items sold (item count).
    Through Return rate This is the ratio between Sales returns and Purchase returns to vendors.

([Purchase Return Rate] / [Return Rate ])

Sales Budget Sales Budget Amount Column [Amount] from Retail Sales Budget Entry] in source data with default aggregate set to SUM
    Closing Stock Value [Closing Stock Value] from Retail Sales Budget Entry] in source data with default aggregate set to SUM
    Code [Code] from Retail Sales Budget Entry] in source data
    Cost Amount [Cost Amount] from Retail Sales Budget Entry] in source data with default aggregate set to SUM

 

  CY Budget Budget sum for Current year ( This needs looking into as Current year is set as Max year - 1)

 

  Discount Amount [Discount Amount] from Retail Sales Budget Entry] in source data with default aggregate set to SUM

 

  Negative Adjustment Amount [Negative Adjustment Amount] from Retail Sales Budget Entry] in source data with default aggregate set to SUM

 

  Opening Stock Value [Opening Stock Value] from Retail Sales Budget Entry] in source data with default aggregate set to SUM

 

  Quantity [Quantity] from Retail Sales Budget Entry] in source data with default aggregate set to SUM

 

  Revision [Revision] from Retail Sales Budget Entry] in source data with default aggregate set to SUM

 

  UserID [User ID] from Retail Sales Budget Entry] in source data

Staff

Sales First Name First name of Staff member.
Can either be [First Name] from source table [staff] or first name from [Name] in source table [Salesperson_Purchaser]

 

  ID ID of Staff member.
Can either be [ID] from source table [staff] or [Code] from source table [Salesperson_Purchaser]

 

  Last Name Last name of Staff member.
Can either be [Last Name] from source table [staff] or last name from [Name] in source table [Salesperson_Purchaser]

 

  Staff Staff = Staff[LastName]

 

  Transacting Staff Calculated measure showing the distinct count of transacting staff

Store

Sales City [City] from source table [Store]

 

  Country Code [Post Code] from source table [Store]
    County [County] from source table [Store]
    Location Code [Location Code] from source table [Store]
    Post Code [Post Code] from source table [Store]
      Store Name = Store[StoreName]
    Store Name [Name] from source table [Store]
Supply Inventory AVG Days of Supply Average days of supply in the filtered period.
    AVG Sales Last X Days Calculates the average sales in the last number of days based on the What If parameter "Number of days".
    AVG Sales Last X Weeks Calculates the average sales in the last number of weeks based on the What If parameter "Number of weeks".
    AVG Weeks of Supply The average weeks of supply in the filtered period.
    Bottom 10 The 10 items you have the fewest [days of supply] left.

 

  Days of Supply Measures the average number of days the company holds its inventory before selling it.
    Item Supply Rank ASC Items ranked with the lowest days of supply. Filtered on items with days of supply greater than 0.
    Item Supply Rank DESC Items ranked with the highest days of supply.
    Top 10 The 10 items you have the most [days of supply].
    Weeks of Supply Measures the average number of weeks the company holds its inventory before selling it.

TD Margin

Sales MTD Margin Month To Date margin
    QTD Margin Quarter To Date margin
    YTD Margin Year To Date margin
TD Net Sales Sales MOMTD net sales Net Sales change from previous month
    MTD net sales Month To Date Net Sales
    QTD net sales Quarter To Date Net Sales
    YTD net sales Year To Date Net Sales

 

  YTD Days Count of YTD days

Time

Sales AMPM Text, either AM or PM

 

  Hour12 Hour 00 - 12

 

  Hour24 Hour 00 - 23

 

  Minute Minute 00 - 59

 

  Second Second 00 - 59

 

  Time Time in the format hh:mm:ss

Vendor

Inventory Address Registered address for vendors

 

  Blocked Blocked code

1 = Payment

2 = All

 

  City City of vendor

 

  Country Code Vendor’s country code.
    County Vendor’s county.
    Currency Code Vendor’s currency code.

 

  Language Code Vendor’s language.
    Post Code Vendor’s post code.
    Vendor Name Vendor’s name.
    Vendor No Vendor’s number in the NAV system.
    Vendor No&Name Vendor’s number and name combined.