• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 66
  • Last Modified:

Getting mtd to work - Model

Hi

I can't get my MTD column to work when using the Model functionality
I want the MTD to reset when any of the columns differ from columns 1 to 5
Any help appreciated - see below for my example code.

Thanks


SELECT *
FROM
(
SELECT 
'Corporate' AS COL1,'CS' AS COL2,'Global' AS COL3,'ZZZ' AS COL4,'USD' AS COL5,TO_DATE('5/28/2013' ,'mm/dd/YYYY')AS DATES,48927664 AS NAVS
FROM
DUAL
UNION
SELECT 
'Corporate','CS','Global','ZZZ' ,'USD' ,TO_DATE('7/31/2013' ,'mm/dd/YYYY'),49255158.97 
FROM
DUAL
UNION
SELECT 
'Corporate','CS' ,'Global','ZZZ','USD',TO_DATE('8/30/2013' ,'mm/dd/YYYY'),48145696.3 
FROM
DUAL
UNION
SELECT 
'Corporate','CS','Global','ZZZ','USD',TO_DATE('9/30/2013' ,'mm/dd/YYYY'),4915349
FROM
DUAL
-----------------------------------------------------------------------------------
UNION
SELECT 
'Test' AS COL1,'CS' AS COL2,'Global' AS COL3,'AA' AS COL4,'USD' AS COL5,TO_DATE('5/28/2013' ,'mm/dd/YYYY')AS DATES,48927664 AS NAVS
FROM
DUAL
UNION
SELECT 
'Test','CS','Global','AA' ,'USD' ,TO_DATE('7/31/2013' ,'mm/dd/YYYY'),49255158.97 
FROM
DUAL
UNION
SELECT 
'Test','CS' ,'Global','AA','USD',TO_DATE('8/30/2013' ,'mm/dd/YYYY'),48145696.3 
FROM
DUAL
UNION
SELECT 
'Test','CS','Global','AA','USD',TO_DATE('9/30/2013' ,'mm/dd/YYYY'),4915349
FROM
DUAL
)
MODEL
DIMENSION BY (COL1,COL2,COL3,COL4,COL5,DATES)
MEASURES (0 MTD, NAVS)
RULES SEQUENTIAL ORDER
( -- MTD = ThisMonth / PrevMonth - 1
-- If monthly performance is worse -100% (impossible) then assume bad data and return null
MTD [ANY, ANY, ANY, ANY, ANY, ANY]
ORDER BY DATES ASC =
CASE
WHEN ( NAVS[CV(),CV(),CV(),CV(),CV(),CV()]
/ NULLIF (NAVS[CV(), CV(),CV(),CV(),CV(),ADD_MONTHS(CV(), -1)],
0)
- 1) <= -1
THEN
NULL
ELSE
NAVS[CV(),CV(),CV(),CV(),CV(),CV()]
/ NULLIF(NAVS[CV(),CV(),CV(),CV(),CV(),ADD_MONTHS(CV(), -1)], 0)
- 1
END

);

Open in new window

0
PHIL Sawyer
Asked:
PHIL Sawyer
  • 2
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
This are your inputs:

|   |   COL1    | COL2 |  COL3  | COL4 | COL5 |        DATES        |    NAVS     |
|---|-----------|------|--------|------|------|---------------------|-------------|
| 1 | Corporate | CS   | Global | ZZZ  | USD  | 28.05.2013 00:00:00 | 48927664.00 |
| 2 | Corporate | CS   | Global | ZZZ  | USD  | 31.07.2013 00:00:00 | 49255158.97 |
| 3 | Corporate | CS   | Global | ZZZ  | USD  | 30.08.2013 00:00:00 | 48145696.30 |
| 4 | Corporate | CS   | Global | ZZZ  | USD  | 30.09.2013 00:00:00 |  4915349.00 |
| 5 | Test      | CS   | Global | AA   | USD  | 28.05.2013 00:00:00 | 48927664.00 |
| 6 | Test      | CS   | Global | AA   | USD  | 31.07.2013 00:00:00 | 49255158.97 |
| 7 | Test      | CS   | Global | AA   | USD  | 30.08.2013 00:00:00 | 48145696.30 |
| 8 | Test      | CS   | Global | AA   | USD  | 30.09.2013 00:00:00 |  4915349.00 |

Open in new window

What are your expecting as the output?

 http://rextester.com/EMXGSN27842

The given model produces this result:
+----+-----------+------+--------+------+------+---------------------+------+-------------+
|    |   COL1    | COL2 |  COL3  | COL4 | COL5 |        DATES        | MTD  |    NAVS     |
+----+-----------+------+--------+------+------+---------------------+------+-------------+
|  1 | Test      | CS   | Global | AA   | USD  | 28.05.2013 00:00:00 | NULL | 48927664.00 |
|  2 | Test      | CS   | Global | AA   | USD  | 31.07.2013 00:00:00 | NULL | 49255158.97 |
|  3 | Test      | CS   | Global | AA   | USD  | 30.08.2013 00:00:00 | NULL | 48145696.30 |
|  4 | Test      | CS   | Global | AA   | USD  | 30.09.2013 00:00:00 | NULL |  4915349.00 |
|  5 | Corporate | CS   | Global | ZZZ  | USD  | 28.05.2013 00:00:00 | NULL | 48927664.00 |
|  6 | Corporate | CS   | Global | ZZZ  | USD  | 31.07.2013 00:00:00 | NULL | 49255158.97 |
|  7 | Corporate | CS   | Global | ZZZ  | USD  | 30.08.2013 00:00:00 | NULL | 48145696.30 |
|  8 | Corporate | CS   | Global | ZZZ  | USD  | 30.09.2013 00:00:00 | NULL |  4915349.00 |
+----+-----------+------+--------+------+------+---------------------+------+-------------+

Open in new window

But I don;t understand what you expect in that MTD column
0
 
PHIL SawyerAuthor Commented:
Hi
I would expect to see in the MTD column the percentage increase or decrease from the previous month - so, if in row 1 the NAV was 100 and in the second row the NAV was 150 then in the MTD column for the first row it would be zero as it is the first row and in the second row it should show 50 as it would be a 50 % increase from 100 to 150. This logic should then apply to the last row. Is there something wrong in the MTD logic?
Regards
0
 
PortletPaulfreelancerCommented:
I'm not familiar with the MODEL clause regrettably, but in playing all I can discover is that as soon as you place ADD_MONTHS(CV(),-1) into that case expression it produces a NULL result. Whilst it probably isn't surprising butADD_MONTHS(CV(), 0) works fine, so it is the NULL date in the "previous month" that is affecting your calculation.
0
 
PHIL SawyerAuthor Commented:
Thanks for the advice re your last comment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now