Concatenate a selection, i.e Month and Month YTD

TM1 has been so successful over the years, not just thanks to its powerful calculation engine but also thanks to its very mature Excel add-in, Perspectives. With Perspectives, you can slice your data from a cube into Excel and then you have the freedom to customize your report by adding formulas or conditional formatting. Perspective uses proper Excel formulas which are very easy to use such as SUBNM to get a list of elements or DBR to get a value from a cube.

In Canvas we kept the same logic, for each TM1 formula, there is an equivalent Canvas directive. For example, if you want to create a  drop down list from a list of elements, in TM1 you would use a SUBNM function and in Canvas you would use the tm1-ui-subnm.

We built Canvas with the TM1 developers in mind. The structure of your Canvas page is similar to what TM1 developers are used to. For example, we are going to show you in this article how easy it is to show side by side the monthly and YTD value of a specific month selected from a dropdown list.

Show the monthly and YTD value of a period selected in a drop-down list in the same table:

In TM1 Perspectives, you will firstly use the SUBNM function to create the Month selection from the subset “months”:

• SUBNM(“canvas_sample:Period”,”months”,”Nov”,”Description”)

Then for the first column header, you just need to reference the cell containing the SUBNM:

• B8

and for the second header where we want the YTD value we need to concatenate the cell containing the SUBNM with (&” YTD”)

• B8 &” YTD”

Building this in Canvas is very similar to building it with Perspectives. First we need to create the selection using the SUBNM Canvas directives, tm1-ui-subnm:

<tm1-ui-subnm tm1-instance="dev"               tm1-dimension="Period"               tm1-subset="months"               tm1-default-element="Nov"               tm1-attribute="Description"               ng-model="page.period"></tm1-ui-subnm>

In Canvas, we store the value of a SUBMN using the ng-model attribute, in our example the value is stored in a variable called page.period.

To get the month description in the first column header, we are going to use the DBRA directive, tm1-ui-dbra:

<tm1-ui-dbra tm1-instance="dev"              tm1-dimension="Period"              tm1-element={{page.period}}              tm1-attribute="Description"              ng-model="page.periodAlias"> </tm1-ui-dbra>

We store the value of our DBRA in the variable page.periodAlias so we can use it easily in our page. For the second header, we need to concatenate the new variable we just created with the YTD:

• {{page.periodalias +  ‘ YTD’}}

To get the value from a cube in Canvas, we use the DBR directive, tm1-ui-dbr. The period in the DBR of our first column will refer to our selection page.period:

<tm1-ui-dbr        tm1-instance="dev"        tm1-cube="Retail"        tm1-elements="Actual,2011/12,{{page.period}},Local,Total Europe,All Products by Category,Sales Amount" ></tm1-ui-dbr>

and for the second column in the DBR we’ll get the YTD value by concatenating page.period with YTD:

<tm1-ui-dbr        tm1-instance="dev"        tm1-cube="Retail"        tm1-elements="Actual,2011/12,{{page.period + 'YTD'}},Local,Total Europe,All Products by Category,Sales Amount" ></tm1-ui-dbr>

That’s it, now you can pick a month and in the same table, you will see the monthly and YTD value: