en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Páll Bjarkason  
#1 Posted : Tuesday, February 10, 2015 11:44:22 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 154

The youtargit.com forum has really enriched my TARGIT journey and I feel obliged to make a small contribution to the community.

One of the everyday awesome features of TARGIT BI Suite is the ability to change object type, with a click of a button. There is a solid range of object types to choose from, covering most cases.
Working as a TARGIT specialist and given my combined background in economics and computer science I recognized an everyday need for an object type known as “The waterfall chart”, “Flying bricks chart” or “Mario chart”. Success has many fathers.

A waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. For obvious reasons this visualization feature is widely used in the field of economics. (Wikipedia)
In what follows I will make an attempt in narrowing the gap and propose a method to implement a generic waterfall chart via the built custom calculation feature of TARGIT BI Suite.

To give an idea of the visitation aim I will start with a screenshot of the final result. In days to come the complete waterfall chart solution will be presented.

UserPostedImage

The calculations "Budget total" (blue) and FloatingMiracle (green/red) are visible in the chart.
Budget total is the left most column and the FloatingMiracle is the little chart piece which is visible in all periods.
The HiddenPillar is white blending in with the background and therefor "invisible". The FloatingMiracle rests on either the Budget total or the HiddenPillar.
The chart type used is multi series stacked.
A Color agent is used to color the negative FlotatinMiracle red.
It should be pointed out that this is a Custom Calculation Generic (Y+) Waterfall Chart. I.e. the chart visualization will fail in the Y- space.
On the other hand the chart dynamically supports any number of columns.

UserPostedImage

The original chart has one measure and one period dimension. The period dimension can be changed to anay other dimension. I.e. salesperson, regions etc.
As can be seen beneath, most calculations are intermediate and therefor hidden.


<rowCalculations>
<formula>
<title>Budget total</title>
<expression>if allcount(d1, d1:0, m1) = 1 and sum(d1, d1, m1) &gt; 0 then sum(d1, d2:d-1, m1) else if allcount(d1, d1:0, m1) = 1 and sum(d1, d1, m1) &lt; 0 then sum(d1, d2:d-1, m1) - (-sum(d1, d1, m1)) else 0</expression>
<color>005AA1</color>
</formula>
<formula>
<title>RunningTotal</title>
<expression>sum(d-1, d-1:0, m1)</expression>
<color>97BE0D</color>
<hidden>true</hidden>
</formula>
<formula>
<title>NetValue</title>
<expression>sum(d-1, 0, m1)</expression>
<color>1065AB</color>
<hidden>true</hidden>
</formula>
<formula>
<title>+Net</title>
<expression>if sum(d-1, 0, m1) &gt; 0 then sum(d-1, 0, m1) else 0</expression>
<color>682069</color>
<hidden>true</hidden>
</formula>
<formula>
<title>-Net</title>
<expression>if sum(d-1, 0, m1) &lt;= 0 then sum(d-1, 0, m1) else 0</expression>
<color>E7511E</color>
<hidden>true</hidden>
</formula>
<formula>
<title>+/- flag</title>
<expression>if sum(c4, 0, m1) &lt;&gt; 0 then 1 else -1</expression>
<color>C7D100</color>
<hidden>true</hidden>
</formula>
<formula>
<title>FloatingMiracle</title>
<expression>ABS:((sum(c3, 0, m1, 0) ^ 2) ^ 0,5)</expression>
<color>6FB121</color>
</formula>
<formula>
<title>HiddenPillar</title>
<expression>if allcount(d1, d1:0, m1) = 1 then 0 else if O1:(sum(c6, 1, m1) &gt; 0 and sum(c6, 0, m1) &gt; 0) then sum(c2, 1, m1) else if O2:(sum(c6, 1, m1) &gt; 0 and sum(c6, 0, m1) &lt; 0) then sum(c2, 0, m1) else if O3:(sum(c6, 1, m1) &lt; 0 and sum(c6, 0, m1) &gt; 0) then sum(c2, 1, m1) else if O4:(sum(c6, 1, m1) &lt; 0 and sum(c6, 0, m1) &lt; 0) then sum(c2, 0, m1) else 11</expression>
<color>FFFFFF</color>
</formula>
</rowCalculations>

Edited by user Tuesday, February 17, 2015 10:07:52 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error