en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Lars Sørensen  
#1 Posted : Wednesday, September 26, 2012 1:14:01 PM(UTC)
Lars Sørensen
Rank: Member

Groups: extranet\Forum
Joined: 7/12/2011(UTC)
Posts: 7

Hi, how do I make a "total" calculation row/visibility row, that has different formulas, depending on which column is should take the data from?

UserPostedImage

The red box should be 0 or empty, the green box is a SUM formula and the blue is a AVG formula.

I only have one red box, but may need more blue then the one shown, the rest is pure SUM formula.
Can I make a calcalution row, which formula take data from the different cells depending on the column?

Edited by user Wednesday, September 26, 2012 1:18:09 PM(UTC)  | Reason: Image not shown

Ole  
#2 Posted : Wednesday, September 26, 2012 1:49:23 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

Hi Lars,

I am assuming that your columns are all measure columns. You can then use this formula to determine which measure column your are currently in:

allcount(d1, d1, m1:0)

If inserted as a calculated row, this formula will return '1' in the first measure column, '2' in the second etc.

Combined with if-then-else statements you can now produce conditional calculations like this one:

UserPostedImage

Best regards,
Ole

Edited by user Wednesday, September 26, 2012 1:52:00 PM(UTC)  | Reason: Not specified

I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Lars Sørensen  
#3 Posted : Thursday, September 27, 2012 9:18:21 AM(UTC)
Lars Sørensen
Rank: Member

Groups: extranet\Forum
Joined: 7/12/2011(UTC)
Posts: 7

Hi Ole

That worked as a charm, but I have one question more... :-)

If I want of make af formula in the then part, how do I type that?
For instance if I want something like this:
if allcount(d1, d1, m1:0) = 4 then (column 1 * column 2 / column 3) else 0
Ole  
#4 Posted : Thursday, September 27, 2012 9:59:07 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

I assume that with "column 1", "column 2" and "column 3" you want to refer to the previous three values of the same calculation...?

In that case the syntax would be something like this:

if allcount(d1, d1, m1:0) = 4 then sum(0, 0, m1) * sum(0, 0, m2) / sum(0, 0, m3)
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Lars Sørensen  
#5 Posted : Thursday, September 27, 2012 2:18:16 PM(UTC)
Lars Sørensen
Rank: Member

Groups: extranet\Forum
Joined: 7/12/2011(UTC)
Posts: 7

No, not entially. In this case, colunm 1, 2 and 3 is 3 different colunms. But perhaps I could just change m3 til the right colunm number...

[update]
Yes, chaning the m to the right colunm worked.
[/update]

Edited by user Thursday, September 27, 2012 2:24:24 PM(UTC)  | Reason: Not specified

Ole  
#6 Posted : Thursday, September 27, 2012 2:24:00 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

I know. Maybe I should have been more clear:

I assume that with "column 1", "column 2" and "column 3" you want to refer to the previous three values, i.e. the three other measure columns, of the same calculated row...?

If this assumption is correct, then my avove suggestion is still valid. Have you tried it?

But on the other hand - maybe I am still wrong.
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Lars Sørensen  
#7 Posted : Thursday, September 27, 2012 2:27:51 PM(UTC)
Lars Sørensen
Rank: Member

Groups: extranet\Forum
Joined: 7/12/2011(UTC)
Posts: 7

Ahh, you where right the first time, as allways... :-)

I just misunderstood it, as you meant the three colunms I refered to in my first post.

I think I have everything covered now, just have to check all calculations. But if I need more help, I'll write it here or start a new post.

Thanks for the help.
Lars Sørensen  
#8 Posted : Wednesday, October 3, 2012 4:13:58 PM(UTC)
Lars Sørensen
Rank: Member

Groups: extranet\Forum
Joined: 7/12/2011(UTC)
Posts: 7

Hi Ole

Just ran into another question.

In one of the formulas, I can get the situation, where it tries to divide with 0. I thought, that I could just put in a new if-then-else inside another if-then-else, but TARGIT will not agree with me on how to do this.

My try is something like this (a part of the full formula):
if allcount(d1, d1, m1:0) = 14 then
if avg(0, all, 0) = 0 then
0
else avg(0, all, 0)
else if allcount(d1, d1, m1:0) = 15 then
.
.
.
.

Is it possible to have an IF inside another IF?
Ole  
#9 Posted : Wednesday, October 3, 2012 4:21:27 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

I think you just need to put it into a couple of brackets:

if allcount(d1, d1, m1:0) = 14 then
(if avg(0, all, 0) = 0 then 0 else avg(0, all, 0))
else
if allcount(d1, d1, m1:0) = 15 then
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Lars Sørensen  
#10 Posted : Thursday, October 4, 2012 1:15:06 PM(UTC)
Lars Sørensen
Rank: Member

Groups: extranet\Forum
Joined: 7/12/2011(UTC)
Posts: 7

As allways it is a simple solution, when you look at it. Just can't believe I didn't try with brackets myself.
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