Forecasting
Seasonal Sales? Not a Problem.
June 28, 2017
There is a client who has a reporting issue problem. Let’s call him Ray. His annual sales are to be split into summer and winter sales, but on a seasonal, not calendar basis. In Ray’s world, winter begins in November and ends in April spanning two calendar years. While Ray sounded calm when he called me, I could feel the tear of frustration welling up in his eyes. How can we summarize annual winter sales in MetrixND when the sales fall in two calendar years?
Let’s be more specific. The picture to the right shows the monthly sales. Ray wants to call November 1995 through April 1996 the Winter 1995 sales.
Typically, the MetrixND’s SumAcross function is used to convert monthly data to annual total. The process takes two steps. First, the monthly sales are split into calendar summer and winter sales in a monthly transformation table. Second, the annual sums are calculated using the SumAcross function in an annual transformation table. The steps, transformations, and results are show below.
But, this is not what Ray wants to do. To use MetrixND’s data transformation capabilities, Ray needs to move the January 1996 through April 1996 values into the January 1995 through April 1995 positions as show below. If Ray can do this, then the annual transformation technique works.
The good news is that Ray called and I have a solution.
Using the following transform, I can move January to April sales using the Lead function.
Once I move the data, I can use the SumAcross function, just like before, to summarize annual summer and winter sales leaving Ray very happy.
Let’s be more specific. The picture to the right shows the monthly sales. Ray wants to call November 1995 through April 1996 the Winter 1995 sales.
Typically, the MetrixND’s SumAcross function is used to convert monthly data to annual total. The process takes two steps. First, the monthly sales are split into calendar summer and winter sales in a monthly transformation table. Second, the annual sums are calculated using the SumAcross function in an annual transformation table. The steps, transformations, and results are show below.
But, this is not what Ray wants to do. To use MetrixND’s data transformation capabilities, Ray needs to move the January 1996 through April 1996 values into the January 1995 through April 1995 positions as show below. If Ray can do this, then the annual transformation technique works.
The good news is that Ray called and I have a solution.
Using the following transform, I can move January to April sales using the Lead function.
Once I move the data, I can use the SumAcross function, just like before, to summarize annual summer and winter sales leaving Ray very happy.
Si è verificato un errore nell'elaborarazione del modello.
The following has evaluated to null or missing:
==> authorContent.contentFields [in template "44616#44647#114455" at line 9, column 17]
----
Tip: It's the step after the last dot that caused this error, not those before it.
----
Tip: If the failing expression is known to legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing</#if>. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)??
----
----
FTL stack trace ("~" means nesting-related):
- Failed at: contentFields = authorContent.content... [in template "44616#44647#114455" at line 9, column 1]
----
1<#assign
2 webContentData = jsonFactoryUtil.createJSONObject(author.getData())
3 classPK = webContentData.classPK
4/>
5
6<#assign
7authorContent = restClient.get("/headless-delivery/v1.0/structured-contents/" + classPK + "?fields=contentFields%2CfriendlyUrlPath%2CtaxonomyCategoryBriefs")
8contentFields = authorContent.contentFields
9categories=authorContent.taxonomyCategoryBriefs
10authorContentData = jsonFactoryUtil.createJSONObject(authorContent)
11friendlyURL = authorContentData.friendlyUrlPath
12authorCategoryId = "0"
13/>
14
15<#list contentFields as contentField >
16 <#assign
17 contentFieldData = jsonFactoryUtil.createJSONObject(contentField)
18 name = contentField.name
19 />
20 <#if name == 'authorImage'>
21 <#if (contentField.contentFieldValue.image)??>
22 <#assign authorImageURL = contentField.contentFieldValue.image.contentUrl />
23 </#if>
24 </#if>
25 <#if name == 'authorName'>
26 <#assign authorName = contentField.contentFieldValue.data />
27 <#list categories as category >
28 <#if authorName == category.taxonomyCategoryName>
29 <#assign authorCategoryId = category.taxonomyCategoryId />
30 </#if>
31 </#list>
32 </#if>
33 <#if name == 'authorDescription'>
34 <#assign authorDescription = contentField.contentFieldValue.data />
35
36 </#if>
37
38 <#if name == 'authorJobTitle'>
39 <#assign authorJobTitle = contentField.contentFieldValue.data />
40
41 </#if>
42
43</#list>
44
45<div class="blog-author-info">
46 <#if authorImageURL??>
47 <img class="blog-author-img" id="author-image" src="${authorImageURL}" alt="" />
48 </#if>
49 <#if authorName??>
50 <#if authorName != "">
51 <p class="blog-author-name">By <a id="author-detail-page" href="/w/${friendlyURL}?filter_category_552298=${authorCategoryId}"><span id="author-full-name">${authorName}</span></a></p>
52 <hr />
53 </#if>
54 </#if>
55 <#if authorJobTitle??>
56 <#if authorJobTitle != "">
57 <p class="blog-author-title" id="author-job-title" >${authorJobTitle}</p>
58 <hr />
59 </#if>
60 </#if>
61 <#if authorDescription??>
62 <#if authorDescription != "" && authorDescription != "null" >
63 <p class="blog-author-desc" id="author-job-desc">${authorDescription}</p>
64 <hr />
65 </#if>
66 </#if>
67</div>
The following has evaluated to null or missing:
==> authorContent.contentFields [in template "44616#44647#114455" at line 9, column 17]
----
Tip: It's the step after the last dot that caused this error, not those before it.
----
Tip: If the failing expression is known to legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing</#if>. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)??
----
----
FTL stack trace ("~" means nesting-related):
- Failed at: contentFields = authorContent.content... [in template "44616#44647#114455" at line 9, column 1]
----
1<#assign
2 webContentData = jsonFactoryUtil.createJSONObject(author.getData())
3 classPK = webContentData.classPK
4/>
5
6<#assign
7authorContent = restClient.get("/headless-delivery/v1.0/structured-contents/" + classPK + "?fields=contentFields%2CfriendlyUrlPath%2CtaxonomyCategoryBriefs")
8contentFields = authorContent.contentFields
9categories=authorContent.taxonomyCategoryBriefs
10authorContentData = jsonFactoryUtil.createJSONObject(authorContent)
11friendlyURL = authorContentData.friendlyUrlPath
12authorCategoryId = "0"
13/>
14
15<#list contentFields as contentField >
16 <#assign
17 contentFieldData = jsonFactoryUtil.createJSONObject(contentField)
18 name = contentField.name
19 />
20 <#if name == 'authorImage'>
21 <#if (contentField.contentFieldValue.image)??>
22 <#assign authorImageURL = contentField.contentFieldValue.image.contentUrl />
23 </#if>
24 </#if>
25 <#if name == 'authorName'>
26 <#assign authorName = contentField.contentFieldValue.data />
27 <#list categories as category >
28 <#if authorName == category.taxonomyCategoryName>
29 <#assign authorCategoryId = category.taxonomyCategoryId />
30 </#if>
31 </#list>
32 </#if>
33 <#if name == 'authorDescription'>
34 <#assign authorDescription = contentField.contentFieldValue.data />
35
36 </#if>
37
38 <#if name == 'authorJobTitle'>
39 <#assign authorJobTitle = contentField.contentFieldValue.data />
40
41 </#if>
42
43</#list>
44
45<div class="blog-author-info">
46 <#if authorImageURL??>
47 <img class="blog-author-img" id="author-image" src="${authorImageURL}" alt="" />
48 </#if>
49 <#if authorName??>
50 <#if authorName != "">
51 <p class="blog-author-name">By <a id="author-detail-page" href="/w/${friendlyURL}?filter_category_552298=${authorCategoryId}"><span id="author-full-name">${authorName}</span></a></p>
52 <hr />
53 </#if>
54 </#if>
55 <#if authorJobTitle??>
56 <#if authorJobTitle != "">
57 <p class="blog-author-title" id="author-job-title" >${authorJobTitle}</p>
58 <hr />
59 </#if>
60 </#if>
61 <#if authorDescription??>
62 <#if authorDescription != "" && authorDescription != "null" >
63 <p class="blog-author-desc" id="author-job-desc">${authorDescription}</p>
64 <hr />
65 </#if>
66 </#if>
67</div>