Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
HTML
<script>
  AJS.toInit(function(){
    if (AJS.params.remoteUser == ''){
      AJS.$('#header').hide();
      AJS.$('#main-header').hide();
    }
  });
</script>
<iframe id="topheader" src="https://www.targit.com/layouts/targit13/doc_confluence.aspx" scrolling="no" style="width:100%;height:467px;overflow:hidden;scrolling:no;"></iframe>

 Back


Calculations Expert Course

The Syntax, explained

The syntax is about being able to refer to Columns, Rows and Measures within a crosstab.

The general syntax is then function(column, row, measure).



HTML
<div style="margin-left: 30.0px">

<iframe src="https://player.vimeo.com/video/191467686" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>

</div>

Simple calculations in crosstab

A couple of examples on simple calculations in a crosstab:

    • "Difference" as a calculated column.
    • "Average" as a calculated row.

The first calculated column inserted in this way may later be referred to as e.g. sum(c1, 0, m1).

The first calculated row inserted in this way may later be referred to as e.g. sum(0, c1, m1).


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191469922" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

Calculation "as a new measure"

When you insert a calculation "as a new measure" in a crosstab that already have e.g. 2 measures, m1 and m2, then the new calculation can be referred to as m3.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191470606" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

Calculations in graphical charts

Calculations in graphs are not different from calculations in crosstabs. In fact, your syntax still refer to the crosstab behind tha graph.

This example will show you how to add calculations to graphs. Various average calculations, including different range references, are demonstrated.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191472757?app_id=122963" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

Named Dimension Member Reference

The Named Dimension Member Reference method is useful when you want to refer to a specific row member or a specific column member in your data set.

Sometimes a reference to eg. "the fifth row" is not what you want, because one day that will refer to one customer, but on another day - due to data changed in the meantime - that may refer to another customer.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191772084" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

If-then-else statements

You can make conditional calculations on basis of your data values by use of if-then-else statements.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191487520" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

The fourth syntax reference - The "Undefined"

Sometimes you will experience that your calculations return an "Undefined" result. This could happen if you are using a relative reference to something outside the current data scope.

To prevent "Undefined" from appearing, you can work with a fourth parameter in your calculation syntax.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191489048" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

The Count and Allcount functions

The Count function will count all non-empty cells within the reference range.

The Allcount function will count all cells, empty and non-empty, within the reference range.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191773543" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

The Visibility Modifier

When your data set in the TARGIT client is influenced by visibility option, i.e. some data have been hidden, you may need to add Visibility Modifiers, (v) or (h), to your calculations.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191774910" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

The Order Modifier

When data in the TARGIT client has been sorted, eg. descending or ascending, you may need to use Order modifiers in your calculations.

In this way you can make your calculation work according to the original, unsorted order - while at the same time display data in a sorted order.

The Order modifier may also be necessary to include if you intend to sort your data according to calculated values.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191788040" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

Hierarchy modifiers: Children

A multilevel hierarchy can be viewed as a tree structure with nodes and branches. Each node at a higher level may have a number of children at a lower level.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191788844" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

The Siblings modifier

In a hierarchy, dimension members with a common parent node can be referred to as Siblings.

When added as a modifier to the calculation syntax, you can then start calculating eg. averages within individual groups of data in the same crosstab.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191789349?app_id=122963" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

The Level modifier

Individual levels of a hierarchical dimension can be referred to as Level 1, Level 2 etc.

In most cases, the default behavior of a TARGIT calculation, i.e. to work on the current level is what you want. But, with the Level modifier, you can focus your calculation to a specific level of a hierarchy.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191789961" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>

The Level modifier in a Custom Hierarchy

Custom Hierarchy is a hierarchy that you have created eg. by adding to different dimensions to the same axis of a crosstab.

The Level modifier will then need to take all dimensions into account as shown in this example.


HTML
<div style="margin-left: 30.0px">
<iframe src="https://player.vimeo.com/video/191790635" noborder="0" width="800" height="450" allow="fullscreen" scrolling="yes" seamless></iframe>
</div>



HTML
<style type="text/css">
      #title-text { display: none;} 
      #breadcrumbs, #footer, #likes-and-labels-container, #comments-section { display:none; }	 

div.theme-default .ia-splitter #main {
    margin-left: 0px;
}
.ia-fixed-sidebar, .ia-splitter-left {
    display: none;
}
div#main {
    margin-left: 0px !important;
}
div#footer {
 
margin-left: 0px !important;
}

.columnLayout.two-equal
{
padding-left: 20px;
padding-right: 20px
}

#main #content
{
padding-right:0px;
}

div#main
{
padding:0px;
}
#page
{
overflow-x: hidden;
}
iframe#topheader
{
border:0px;
}
.contentLayout2 .columnLayout
{
margin-bottom:0px;
}

#workflow-page-message
{
margin-bottom: 0px;
}



</style>
<script src="//cep.targit.com/bundle/beacon"></script>

<div class="Footer" style="background-color: #1d252d;width:100%;">
    <div class="loop" style="padding-top:69px;padding-bottom:72px;text-align:center;">
        <img src="https://targitmedia.azureedge.net/Resources/MainSite/Images/footerloop.png" style="margin-bottom:32px;">
        <div style="font-size:24px;line-height:32px;letter-spacing:0.6px;color:#fff;">
            Courage to Act
        </div>
    </div>
    <div style="width:100%;background-color:#121a23;">
        <div style="width:1080px;margin:auto;padding-bottom:20px;padding-top:20px;">
            <div style="float:left;font-size:13px;color:#8a9298;">
                <div style="margin-top:5px;display:inline-block;padding-right:30px;color:#8a9298;">
                    © 2019 TARGIT. All Rights Reserved.
                </div>
                <a style="margin-top:5px;display:inline-block;text-decoration:none!important;color:inherit;font-size:13px;" href="https://www.targit.com/en/personal-data-policy">Personal Data Policy</a>
            </div>
            <div style="float:right;">
                <a style="vertical-align:super;font-size:13px;letter-spacing:0.6px;margin-right:36px;color:#fff;text-decoration:none!important;text-transform:uppercase" href=".columnLayout.two-equal
{
width: 85%;
margin: auto;
}
</style>

<script src="//cep.targit.com/bundle/beacon"></script>
<iframe id="bottomfooter" src="https://www.targit.com/en/meet-targit/meet-the-targit-team/contact-targit">CONTACT</a>
                <a style="vertical-align:super;font-size:13px;letter-spacing:0.6px;margin-right:36px;color:#fff;text-decoration:none!important;text-transform:uppercase"  href="https://www.targit.com/en/meet-targit/targit-news/news-list">PRESS</a>
                <a style="vertical-align:super;font-size:13px;letter-spacing:0.6px;margin-right:36px;color:#fff;text-decoration:none!important;text-transform:uppercase"  href="https://www.targit.com/en/meet-targit/targit-career/jobs">CAREERS</a>
                <a style="margin-right: 36px !important;text-decoration: none !important;color: inherit;" href="https://twitter.com/targit" target="_blank"><img src="https://targitmedia.azureedge.net/Resources/MainSite/Images/twitterfooter.png"></a>
                <a style="margin-right: 36px !important;text-decoration: none !important;color: inherit;" href="https://www.linkedin.com/company/targit" target="_blank"><img src="https://targitmedia.azureedge.net/Resources/MainSite/Images/linkedinfooter.png"></a>
                <a style="margin-right: 36px !important;text-decoration: none !important;color: inherit;" href="https://www.facebook.com/TARGIT.BI.Suite" target="_blank"><img src="https://targitmedia.azureedge.net/Resources/MainSite/Images/fbookfooter.png"></a>
            </div>
            <div style="float:none;clear:both;"></div>
        </div>
    </div>
</div>




emptyfooter" scrolling="no" frameborder="0" style="width:100%;height:190px;overflow:hidden;scrolling:no;border:0px"></iframe>