December 6th: Smartpad - hidden gems

The Smart has a few hidden gems that you can utilize – here are 3 examples:

  1. The hierarchical collation button, which decided the behavior of sorting in TARGIT on hierarchies

  2. The regression button, which allows you to add linear or best fit regression to timeline data shown in either a column chart or a line chart

  3. Measures down which allows you to show measure on the vertical axis instead of the horizontal axis.

Watch this short video to learn about these 3 hidden gems:

Was this article helpful?
0 out of 0 found this helpful

Comments

3 comments
  • For financial reporting and analysis, the Measures Down feature is very useful. However, I am not able to create a formula that applies to all measures. For example, when showing 10 measures down over two years of data, I want to show the % change between last year and this year. I am having trouble creating a formula that will apply to each measure. Below are a couple views of this issue:

     

    0
  • Hi Ellis,

     “Measures down” is a nice feature if that is the only thing that is required: To show measures vertical instead of horizontal.

    When you start adding more features to the crosstab, such as calculations, then problems will soon arise and workarounds and compromises will be required, if possible.

    So, this is my suggested workaround.

    Note: It is not perfect in the sense that the calculations that you use to replace the original measures can only have one number format, where ideally it should have different number formats in the different columns.

    I start out with a crosstab with three measures (measures down already enabled). Furthermore, I have a User Dimension with three members: Last Year, This Year and a “Growth in %” (no criteria selected) member.

    Next, I am “duplicating” each of these measures with a “as a measure” calculation, prefixed with m4, m5 and m6 respectively in this screenshot:

    The syntaxes are:

    • m4: if allcount(d1:0, 0, m1) <= 2 then sum(0, 0, m1) else (sum(d2, 0, m1) - sum(d1, 0, m1)) / sum(d1, 0, m1) * 100
    • m5: if allcount(d1:0, 0, m2) <= 2 then sum(0, 0, m2) else (sum(d2, 0, m2) - sum(d1, 0, m2)) / sum(d1, 0, m2) * 100
    • m6: if allcount(d1:0, 0, m3) <= 2 then sum(0, 0, m3) else (sum(d2, 0, m3) - sum(d1, 0, m3)) / sum(d1, 0, m3) * 100

    I only have two Year columns. If you have more columns, e.g., 6 columns, you should change the condition to: if allcount(d1:0, 0, m1) <= 6

    Eventually, you can use the standard Visibility settings in the Properties Smartpad to hide the original measures:

    BR / Ole

    0
  • Thanks, Ole. That is a brilliant workaround and it gives the correct results with my data. The solution is somewhat clunky , which is understandable given the constraints you are working with. A more elegant long-term solution would be to allow the measures to behave as dimensions and use the data as the measure when choosing "Measures Down". This should allow one to create calculations for rows or columns based on the data matrix. I understand this is a difficult ask, but it would be very useful for all financial analysis, and might allow for more creative formatting of the results.

    0

Please sign in to leave a comment.