1.
What's the data type of Blog title? The query engine thinks that it is a text data type. Are you sure that all the data in that column is numeric?
2.
My advice for the msr-upl-2122 error is to review the data in the file which you are attempting to use to append and confirm that all the values of phone3 are actually numeric. The error suggests that at least one has text data.
Your relative time problem:
Can you elaborate about what you mean by the visualization with no data and relative time please.
Just to confirm, did you have any thing from your time dimension in the report?
What is the _as_of_date which you have set? Do you have data for that date?
I think I need more information in order to provide guidance but these are the most obvious things to control for.
Relative time measures carry not just the measure but the time filter. Here is a report with relative time measures and something from my time dimension. As you can see you get nulls for anything outside of the bounds of the relative time as defined by the _as_of_date (see below)
If you look at the SQL which is generated you will see a filter for the relative time.
Here is an example. My _as_of_date is April 15, 2012. The report has sales quantity.current month and sales quantity.prior month. As you can see there are two filters which are for the current month and prior month, based on the _as_of_date value. A report which compares the current month versus prior month values for a measure should be built-in with relative time and not need any special additional work.
WITH
SLS_PRODUCT_LOOKUP0 AS
(
SELECT
SLS_PRODUCT_LOOKUP01.PRODUCT_NUMBER AS PRODUCT_NUMBER,
SLS_PRODUCT_LOOKUP01.PRODUCT_LANGUAGE AS PRODUCT_LANGUAGE,
SLS_PRODUCT_LOOKUP01.PRODUCT_NAME AS PRODUCT_NAME,
SLS_PRODUCT_LOOKUP01.PRODUCT_DESCRIPTION AS PRODUCT_DESCRIPTION
FROM
GOSLDW_DNB_DB2..GV1022DW.SLS_PRODUCT_LOOKUP SLS_PRODUCT_LOOKUP01
WHERE
SLS_PRODUCT_LOOKUP01.PRODUCT_LANGUAGE IN (
'EN' )
),
Products_itemNormalGenerated_PRODUCT_KEY AS
(
SELECT
SLS_PRODUCT_LOOKUP0.PRODUCT_NUMBER AS PRODUCT_NUMBER,
SLS_PRODUCT_DIM0.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE,
SLS_PRODUCT_DIM0.PRODUCT_TYPE_KEY AS PRODUCT_TYPE_KEY,
SLS_PRODUCT_DIM0.BASE_PRODUCT_KEY AS BASE_PRODUCT_KEY,
MIN(SLS_PRODUCT_LINE_LOOKUP0.PRODUCT_LINE_EN) AS PRODUCT_LINE_EN,
MIN(SLS_PRODUCT_DIM0.PRODUCT_KEY) AS PRODUCT_KEY
FROM
GOSLDW_DNB_DB2..GV1022DW.SLS_PRODUCT_BRAND_LOOKUP SLS_PRODUCT_BRAND_LOOKUP0
INNER JOIN GOSLDW_DNB_DB2..GV1022DW.SLS_PRODUCT_DIM SLS_PRODUCT_DIM0
ON SLS_PRODUCT_BRAND_LOOKUP0.PRODUCT_BRAND_CODE = SLS_PRODUCT_DIM0.PRODUCT_BRAND_CODE
INNER JOIN GOSLDW_DNB_DB2..GV1022DW.SLS_PRODUCT_COLOR_LOOKUP SLS_PRODUCT_COLOR_LOOKUP0
ON SLS_PRODUCT_COLOR_LOOKUP0.PRODUCT_COLOR_CODE = SLS_PRODUCT_DIM0.PRODUCT_COLOR_CODE
INNER JOIN GOSLDW_DNB_DB2..GV1022DW.SLS_PRODUCT_LINE_LOOKUP SLS_PRODUCT_LINE_LOOKUP0
ON SLS_PRODUCT_LINE_LOOKUP0.PRODUCT_LINE_CODE = SLS_PRODUCT_DIM0.PRODUCT_LINE_CODE
INNER JOIN SLS_PRODUCT_LOOKUP0
ON SLS_PRODUCT_LOOKUP0.PRODUCT_NUMBER = SLS_PRODUCT_DIM0.PRODUCT_NUMBER
INNER JOIN GOSLDW_DNB_DB2..GV1022DW.SLS_PRODUCT_SIZE_LOOKUP SLS_PRODUCT_SIZE_LOOKUP0
ON SLS_PRODUCT_SIZE_LOOKUP0.PRODUCT_SIZE_CODE = SLS_PRODUCT_DIM0.PRODUCT_SIZE_CODE
INNER JOIN GOSLDW_DNB_DB2..GV1022DW.SLS_PRODUCT_TYPE_LOOKUP SLS_PRODUCT_TYPE_LOOKUP0
ON SLS_PRODUCT_TYPE_LOOKUP0.PRODUCT_TYPE_CODE = SLS_PRODUCT_DIM0.PRODUCT_TYPE_CODE
GROUP BY
SLS_PRODUCT_LOOKUP0.PRODUCT_NUMBER,
SLS_PRODUCT_DIM0.PRODUCT_LINE_CODE,
SLS_PRODUCT_DIM0.PRODUCT_TYPE_KEY,
SLS_PRODUCT_DIM0.BASE_PRODUCT_KEY
),
SLS_SALES_FACT0 AS
(
SELECT
SLS_SALES_FACT01.ORDER_DAY_KEY AS ORDER_DAY_KEY,
SLS_SALES_FACT01.PRODUCT_KEY AS PRODUCT_KEY,
CAST(SUBSTRING(CAST(SLS_SALES_FACT01.ORDER_DAY_KEY AS VARCHAR(10)) FROM 1 FOR 4) || '-' || SUBSTRING(CAST(SLS_SALES_FACT01.ORDER_DAY_KEY AS VARCHAR(10)) FROM 5 FOR 2) || '-' || SUBSTRING(CAST(SLS_SALES_FACT01.ORDER_DAY_KEY AS VARCHAR(10)) FROM 7 FOR 2) AS DATE) AS order_day_date,
COALESCE(
SLS_SALES_FACT01.QUANTITY,
0) AS QUANTITY
FROM
GOSLDW_DNB_DB2..GV1022DW.SLS_SALES_FACT SLS_SALES_FACT01
)
SELECT
Products_itemNormalGenerated_PRODUCT_KEY.PRODUCT_LINE_EN AS Product_Line,
SUM(
CASE
WHEN
SLS_SALES_FACT0.order_day_date >= DATE '2012-04-01' AND
SLS_SALES_FACT0.order_day_date < DATE '2012-05-01'
/* this is the current month */
THEN
SLS_SALES_FACT0.QUANTITY
END) AS Current_Month__Quantity_,
SUM(
CASE
WHEN
SLS_SALES_FACT0.order_day_date >= DATE '2012-03-01' AND
SLS_SALES_FACT0.order_day_date < DATE '2012-04-01'
/* this is the prior month */
THEN
SLS_SALES_FACT0.QUANTITY
END) AS Prior_Month__Quantity_
FROM
Products_itemNormalGenerated_PRODUCT_KEY
INNER JOIN SLS_SALES_FACT0
ON Products_itemNormalGenerated_PRODUCT_KEY.PRODUCT_KEY = SLS_SALES_FACT0.PRODUCT_KEY
GROUP BY
Products_itemNormalGenerated_PRODUCT_KEY.PRODUCT_LINE_EN
Your expression should work as it works for me.
Another expression would be
([Current Month [Quantity]]] - [Prior Month [Quantity]]]) / abs([Prior Month [Quantity]]])
------------------------------
IAN HENDERSON
------------------------------
Original Message:
Sent: Thu September 16, 2021 02:56 PM
From: Kat Jarvis
Subject: Month over Month Data in Cognos Analytics
Hi Cognos Community!
We have data that we need to look at Month over Month (comparing the previous month to the current month). Our initial approach was to use a KPI visualization using relative data to compare prior month (ex: Number of messages) as the target value and current month as the base value but the visualization kept showing as no value.
The second route we used was using the expression editor to create calculations based on the data for example:
The calculation is:
((Discussion_Contributors_Per_D.NumberOfMessages.Current_Month - Discussion_Contributors_Per_D.NumberOfMessages.Prior_Month) / (Discussion_Contributors_Per_D.NumberOfMessages.Prior_Month * 1.0))
This hasn't worked correctly for us yet - but the logic should be executable. What is the correct high level logic that you have used for MoM based on the way that Cognos reads the data? Has anyone used running-difference month to month or running-total? Ex running total members and a running difference of members – 1 (difference between last month and this month as a percent)?
Ultimately we need % growth year to date and a filter that cuts dates into months. If we know the % growth YTD from 1 (January) to 8 (August), then should be able to put the month filter on top of it and show that growth month to month instead of YTD.
We have not found a way to successfully get this to work yet. Any thoughts or suggestions would be much appreciated!
There are also 2 errors we've run into:
1. Error on data that comes in as an identifier not a measure and receive a warning when trying to change it to a measure:
2. A few times when appending data we received this error:
Has anyone run into these errors and any thoughts on how to resolve?
------------------------------
Kat Jarvis
------------------------------
#CognosAnalyticswithWatson